<?php
namespace Academy\Traits;

use Academy\Helper;
use WP_Query;
use function _deprecated_function;

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

trait Courses {

	public static function is_course_taxonomy() {
		return is_tax( get_object_taxonomies( 'academy_courses' ) );
	}
	public static function get_course_rating( $course_id ) {
		global $wpdb;

		$ratings = array(
			'rating_count'   => 0,
			'rating_sum'     => 0,
			'rating_avg'     => number_format( 0, 1 ),
			'count_by_value' => array(
				5 => 0,
				4 => 0,
				3 => 0,
				2 => 0,
				1 => 0,
			),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rating = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(meta_value) AS rating_count,
					SUM(meta_value) AS rating_sum
			FROM	{$wpdb->comments}
					INNER JOIN {$wpdb->commentmeta}
							ON {$wpdb->comments}.comment_ID = {$wpdb->commentmeta}.comment_id
			WHERE 	{$wpdb->comments}.comment_post_ID = %d
					AND {$wpdb->comments}.comment_type = %s
					AND meta_key = %s;
			",
				$course_id,
				'academy_courses',
				'academy_rating'
			)
		);

		if ( $rating->rating_count ) {
			$avg_rating = number_format( ( $rating->rating_sum / $rating->rating_count ), 1 );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$stars = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT commentmeta.meta_value AS rating, 
						COUNT(commentmeta.meta_value) as rating_count 
				FROM	{$wpdb->comments} comments
						INNER JOIN {$wpdb->commentmeta} commentmeta
								ON comments.comment_ID = commentmeta.comment_id
				WHERE	comments.comment_post_ID = %d 
						AND comments.comment_type = %s
						AND commentmeta.meta_key = %s
				GROUP BY commentmeta.meta_value;
				",
					$course_id,
					'academy_courses',
					'academy_rating'
				)
			);

			$ratings = array(
				5 => 0,
				4 => 0,
				3 => 0,
				2 => 0,
				1 => 0,
			);
			foreach ( $stars as $star ) {
				$index = (int) $star->rating;
				array_key_exists( $index, $ratings ) ? $ratings[ $index ] = $star->rating_count : 0;
			}

			$ratings = array(
				'rating_count'   => $rating->rating_count,
				'rating_sum'     => $rating->rating_sum,
				'rating_avg'     => $avg_rating,
				'count_by_value' => $ratings,
			);
		}//end if

		return (object) $ratings;
	}

	public static function get_courses_reviews( $course_id, $offset = 0, $limit = 200 ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users -- reporting JOIN on users/usermeta that get_users() cannot express
		$reviews = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$wpdb->comments}.comment_ID, 
					{$wpdb->comments}.comment_post_ID, 
					{$wpdb->comments}.comment_author, 
					{$wpdb->comments}.comment_author_email, 
					{$wpdb->comments}.comment_date, 
					{$wpdb->comments}.comment_content, 
					{$wpdb->comments}.user_id, 
					{$wpdb->commentmeta}.meta_value AS rating,
					{$wpdb->users}.display_name 
			
			FROM 	{$wpdb->comments}
					INNER JOIN {$wpdb->commentmeta} 
					ON {$wpdb->comments}.comment_ID = {$wpdb->commentmeta}.comment_id 
					LEFT JOIN {$wpdb->users}
					ON {$wpdb->comments}.user_id = {$wpdb->users}.ID
			WHERE 	{$wpdb->comments}.comment_post_ID = %d 
					AND comment_type = 'academy_courses' AND meta_key = 'academy_rating'
			ORDER BY comment_ID DESC
			LIMIT 	%d, %d;
			",
				$course_id,
				$offset,
				$limit
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users

		return $reviews;
	}

	public static function get_instructor_ratings( $instructor_id ) {
		global $wpdb;

		$ratings = array(
			'rating_count' => 0,
			'rating_sum'   => 0,
			'rating_avg'   => 0.00,
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rating = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(rating.meta_value) as rating_count, SUM(rating.meta_value) as rating_sum  
			FROM 	{$wpdb->usermeta} courses
					INNER JOIN {$wpdb->comments} reviews
							ON courses.meta_value = reviews.comment_post_ID
						   AND reviews.comment_type = 'academy_courses'
					INNER JOIN {$wpdb->commentmeta} rating
							ON reviews.comment_ID = rating.comment_id
						   AND rating.meta_key = 'academy_rating'
			WHERE 	courses.user_id = %d
					AND courses.meta_key = %s
			",
				$instructor_id,
				'academy_instructor_course_id'
			)
		);

		if ( $rating->rating_count ) {
			$avg_rating = number_format( ( $rating->rating_sum / $rating->rating_count ), 2 );

			$ratings = array(
				'rating_count' => $rating->rating_count,
				'rating_sum'   => $rating->rating_sum,
				'rating_avg'   => $avg_rating,
			);
		}

		return (object) $ratings;
	}

	public static function single_star_rating_generator( $current_rating = 0.00 ) {
		$output = '<span class="academy-group-star">';
		if ( 5 < $current_rating && 0 > $current_rating ) {
			$output .= '<i class="academy-icon academy-icon--star-half"></i>';
		} elseif ( 0 === $current_rating ) {
			$output .= '<i class="academy-icon academy-icon--star-alt"></i>';
		} else {
			$output .= '<i class="academy-icon academy-icon--star"></i>';
		}
		$output .= '</span>';
		return $output;
	}
	public static function star_rating_generator( $current_rating = 0.00 ) {
		$output = '<span class="academy-group-star">';
		for ( $i = 1; $i <= 5; $i++ ) {
			$intRating = (int) $current_rating;

			if ( $intRating >= $i ) {
				$output .= '<i class="academy-icon academy-icon--star" data-rating-value="' . $i . '"></i>';
			} elseif ( ( $current_rating - $i ) === -0.5 ) {
					$output .= '<i class="academy-icon academy-icon--star-half" data-rating-value="' . $i . '"></i>';
			} else {
				$output .= '<i class="academy-icon academy-icon--star-alt" data-rating-value="' . $i . '"></i>';
			}
		}
		$output .= '</span>';
		return $output;
	}

	public static function do_enroll( $course_id, $user_id, $order_id = 0 ) {
		$enrolled = self::is_enrolled( $course_id, $user_id, 'any' );

		if ( ! $course_id || ! $user_id || ( ! empty( $enrolled ) && 'cancel' !== $enrolled->enrolled_status ) ) {
			return false;
		}

		do_action( 'academy/course/before_enroll', $course_id );

		$title = __( 'Course Enrolled', 'academy' ) . ' ' . gmdate( get_option( 'date_format' ) ) . ' @ ' . gmdate( get_option( 'time_format' ) );

		$enrolment_status = 'completed';

		if ( $order_id ) {
			$enrolment_status = apply_filters( 'academy/course/change_enrollment_status', $enrolment_status );
		}

		$enroll_data = apply_filters(
			'academy/course/enroll_data',
			array(
				'post_type'   => 'academy_enrolled',
				'post_title'  => $title,
				'post_status' => $enrolment_status,
				'post_author' => $user_id,
				'post_parent' => $course_id,
			)
		);

		// Insert or update
		if ( ! $enrolled ) {
			$enroll_id = wp_insert_post( $enroll_data, true );
		} else {
			$enroll_data['ID']           = $enrolled->ID;
			$enroll_data['post_date']    = current_time( 'mysql' );
			$enroll_data['post_date_gmt'] = current_time( 'mysql', 1 );
			$enroll_id = wp_update_post( $enroll_data, true );

			if ( \Academy\Helper::get_settings( 'is_reset_academy_enrolled_course_progress' ) ) {
				self::reset_re_enroll_course_progress( $course_id, $user_id );
			}
		}

		// Handle errors
		if ( is_wp_error( $enroll_id ) ) {
			if ( defined( 'WP_DEBUG_LOG' ) && WP_DEBUG_LOG ) {
				error_log( 'Enrollment failed: ' . $enroll_id->get_error_message() ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- opt-in debug log only.
			}
			return false;
		}

		// Clear cache to ensure fresh post object next time
		clean_post_cache( $enroll_id );

		if ( $enroll_id ) {
			// Make Current User as Students
			update_user_meta( $user_id, 'is_academy_student', self::get_time() );
			$user = get_user_by( 'id', $user_id );
			if ( ! in_array( 'academy_student', (array) $user->roles, true ) ) {
				$user->add_role( 'academy_student' );
			}
			if ( $order_id ) {
				$product_id = self::get_course_product_id( $course_id );
				update_post_meta( $enroll_id, 'academy_enrolled_by_order_id', $order_id );
				update_post_meta( $enroll_id, 'academy_enrolled_by_product_id', $product_id );
				update_post_meta( $order_id, 'is_academy_order_for_course', self::get_time() );
				update_post_meta( $order_id, 'academy_order_for_course_id_' . $course_id, $enroll_id );
			}

			if ( 'completed' === $enrolment_status ) {
				do_action( 'academy/course/after_enroll', $course_id, $enroll_id, $user_id );
			}

			return $enroll_id;
		}//end if
		return false;
	}

	public static function reset_re_enroll_course_progress( $course_id, $user_id ) {
		$option_name        = 'academy_course_' . $course_id . '_completed_topics';
		update_user_meta( $user_id, $option_name, wp_json_encode( [] ) );
		$attempt_items = \AcademyQuizzes\Classes\Query::get_attempt_id_by_user_and_course_id( $course_id, $user_id );
		if ( ! empty( $attempt_items ) ) {
			foreach ( $attempt_items as $attempt_item ) {
				\AcademyQuizzes\Classes\Query::delete_quiz_attempt( $attempt_item->attempt_id );
			}
		}
		self::delete_comment_and_review( $course_id, $user_id );
	}

	public static function get_topic_item_by_course_id( $course_id, $type = null ) {
		$topics = self::get_course_curriculum_array( $course_id );
		if ( empty( $topics ) ) {
			return null;
		}
		foreach ( $topics as $value ) {
			if ( $type === $value['type'] ) {
				return $value;
			}
		}
		return null;
	}

	public static function get_course_curriculum_array( $course_id ) {
		$course_curriculum = get_post_meta( $course_id, 'academy_course_curriculum', true );
		$prepare_curriculum = array();
		if ( is_array( $course_curriculum ) ) {
			foreach ( $course_curriculum as $curriculum ) {
				if ( is_array( $curriculum['topics'] ) ) {
					foreach ( $curriculum['topics'] as $topic ) {
						$prepare_curriculum[] = $topic;
					}
				}
			}
		}
		return $prepare_curriculum;
	}

	public static function is_enrolled( $course_id, $user_id, $status = 'completed' ) {
		if ( is_user_logged_in() ) {
			global $wpdb;

			do_action( 'academy/course/is_enrolled_before', $course_id, $user_id );

			$query = $wpdb->prepare(
				"SELECT ID,
					post_author,
					post_date,
					post_date_gmt,
					post_title,
					post_status as enrolled_status
				FROM {$wpdb->posts}
				WHERE post_type = %s
					AND post_parent = %d
					AND post_author = %d",
				'academy_enrolled',
				$course_id,
				$user_id
			);

			if ( 'completed' === $status ) {
				$query .= $wpdb->prepare( ' AND post_status = %s', $status );
			}
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom query with no WP API equivalent
			$getEnrolled = $wpdb->get_row( $query );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

			return apply_filters( 'academy/course/is_enrolled', $getEnrolled, $course_id, $user_id );
		}//end if
		return false;
	}

	public static function get_course_product_id( $course_id ) {
		$product_id = (int) get_post_meta( $course_id, 'academy_course_product_id', true );
		return apply_filters( 'academy/course/get_course_product_id', $product_id, $course_id );
	}

	public static function update_enrollment_status( $course_id, $enrolled_id, $user_id, $status = 'completed' ) {
		if ( ! $course_id || ! $enrolled_id || ! $user_id ) {
			return false;
		}

		global $wpdb;
		$data = array( 'post_status' => $status );
		$where = array(
			'ID' => $enrolled_id,
			'post_author' => $user_id,
			'post_parent' => $course_id
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$is_update = $wpdb->update( $wpdb->posts, $data, $where );
		if ( $is_update && 'completed' === $status ) {
			do_action( 'academy/course/after_enroll', $course_id, $enrolled_id, $user_id );
		}

		return $is_update;
	}

	public static function is_instructor_of_this_course( $instructor_id, $course_id ) {
		global $wpdb;

		if ( ! $instructor_id || ! $course_id ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$instructor = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT umeta_id
			FROM   {$wpdb->usermeta}
			WHERE  user_id = %d
				AND meta_key = 'academy_instructor_course_id'
				AND meta_value = %d
			",
				$instructor_id,
				$course_id
			)
		);

		if ( is_array( $instructor ) && count( $instructor ) ) {
			return $instructor;
		}

		return false;
	}

	public static function is_completed_course( $course_id, $user_id, $allow_details = false, $verification_id = '' ) {
		if ( ! is_user_logged_in() && ! $verification_id ) {
			return apply_filters( 'academy/is_completed_course', false, $course_id, $user_id );
		}

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$is_completed = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT comment_ID, 
					comment_post_ID AS course_id, 
					comment_author AS completed_user_id, 
					comment_date AS completion_date, 
					comment_content AS completed_hash 
			FROM	{$wpdb->comments} 
			WHERE 	comment_agent = %s 
					AND comment_type = %s 
					AND comment_post_ID = %d 
					AND user_id = %d;
			",
				'academy',
				'course_completed',
				$course_id,
				$user_id
			)
		);

		if ( $is_completed ) {
			return apply_filters( 'academy/is_completed_course', $allow_details ? $is_completed : true, $course_id, $user_id );
		}

		return apply_filters( 'academy/is_completed_course', false, $course_id, $user_id );
	}

	public static function get_the_course_category( $ID ) {
		return get_the_terms( $ID, 'academy_courses_category' );
	}

	public static function get_completed_courses_ids_by_user( $user_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT comment_post_ID AS course_id
			FROM	{$wpdb->comments} 
			WHERE 	comment_agent = %s 
					AND comment_type = %s 
					AND user_id = %d;
			",
				'academy',
				'course_completed',
				$user_id
			)
		);
		return $course_ids;
	}

	public static function get_pending_enrolled_courses_ids_by_user( $user_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT post_parent
			FROM 	{$wpdb->posts}
			WHERE 	post_type = %s
					AND post_status = %s
					AND post_author = %d;
			",
				'academy_enrolled',
				'processing',
				$user_id
			)
		);

		return $course_ids;
	}

	public static function get_complete_courses_ids_by_user( $user_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT comment_post_ID
			FROM 	{$wpdb->comments}
			WHERE 	comment_type = %s
					AND comment_approved = %s
					AND comment_author = %d
					AND user_id = %d;",
				'course_completed',
				'approved',
				$user_id,
				$user_id
			)
		);

		return $course_ids;
	}

	public static function get_enrolled_courses_by_user( $user_id, $post_status = 'publish', $offset = 0, $per_page = 10 ) {
		$course_ids = self::get_enrolled_courses_ids_by_user( $user_id );
		if ( count( $course_ids ) ) {
			$course_args = array(
				'post_type'      => 'academy_courses',
				'post_status'    => $post_status,
				'post__in'       => $course_ids,
				'offset'         => $offset,
				'posts_per_page' => $per_page,
			);
			return new WP_Query( $course_args );
		}
		return false;
	}

	public static function get_enrolled_courses_ids_by_user( $user_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT post_parent
				FROM {$wpdb->posts}
				WHERE post_type = %s
				AND post_status = %s
				AND post_author = %d",
				'academy_enrolled',
				'completed',
				$user_id
			)
		);

		return apply_filters( 'academy/enrolled_courses_ids_by_user', $course_ids, (int) $user_id );
	}

	public static function get_total_enrolled_courses_info_by_student_id( int $user_id ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom query with no WP API equivalent
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT *
			FROM {$wpdb->posts} 
			WHERE post_type = %s 
			AND post_author = %d",
			'academy_enrolled',
			$user_id
		), ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		return $results ?? [];
	}

	public static function get_total_enrolled_courses_info_by_student_and_instructor_id( int $student_id, int $instructor_id ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom query with no WP API equivalent
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT DISTINCT p.*
			FROM {$wpdb->posts} AS p
			INNER JOIN {$wpdb->posts} AS pp
				ON pp.ID = p.post_parent
			INNER JOIN {$wpdb->usermeta} AS um 
				ON um.meta_value = pp.ID 
				AND um.meta_key = 'academy_instructor_course_id'
				AND um.user_id = %d
			WHERE p.post_type = 'academy_enrolled'
			AND p.post_author = %d
			AND pp.post_type = 'academy_courses'
			ORDER BY p.post_date DESC",
			$instructor_id,
			$student_id
		), ARRAY_A);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		return $results ?? [];
	}

	public static function get_wishlist_courses_by_user( $user_id, $post_status = 'publish' ) {
		$course_ids = self::get_wishlist_courses_ids_by_user( $user_id );
		if ( count( $course_ids ) ) {
			$course_args = array(
				'post_type'      => [ 'academy_courses', 'alms_course_bundle' ],
				'post_status'    => $post_status,
				'post__in'       => $course_ids,
				'posts_per_page' => count( $course_ids ),
			);
			return new WP_Query( $course_args );
		}
		return false;
	}

	public static function get_wishlist_courses_ids_by_user( $user_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_col( $wpdb->prepare( "SELECT meta_value from {$wpdb->usermeta} WHERE user_id = %d AND meta_key = 'academy_course_wishlist';", $user_id ) );
		return $course_ids;
	}

	public static function get_total_number_of_course_topics( $course_id ) {
		if ( empty( $course_id ) ) {
			return 0;
		}
		$count      = 0;
		$curriculum = get_post_meta( $course_id, 'academy_course_curriculum', true );
		$topics     = wp_list_pluck( $curriculum, 'topics' );

		if ( ! is_array( $topics ) ) {
			return $count;
		}

		foreach ( $topics as $topics_lists ) {
			if ( ! is_array( $topics_lists ) ) {
				continue;
			}

			foreach ( $topics_lists as $list ) {
				if ( isset( $list['topics'] ) && is_array( $list['topics'] ) ) {
					$count += count( $list['topics'] );
					continue;
				}

				++$count;
			}
		}

		return apply_filters(
			'academy/count_total_topics_in_course',
			(int) $count,
			$course_id
		);
	}

	public static function get_total_number_of_completed_course_lessons( $course_id ) {
		if ( empty( $course_id ) ) {
			return 0;
		}

		_deprecated_function( __METHOD__, '2.0.0', 'get_total_number_of_completed_course_topics_by_student_id' );

		$count      = 0;
		$completed_topics = json_decode( get_user_meta( get_current_user_id(), 'academy_course_' . $course_id . '_completed_topics', true ), true );
		if ( is_array( $completed_topics ) && count( $completed_topics ) ) {
			foreach ( $completed_topics as $topics_item ) {
				if ( is_array( $topics_item ) ) {
					$count += count( $topics_item );
				}
			}
		}
		return (int) $count;
	}

	public static function is_course_purchasable( $course_id ) {
		$course_type = self::get_course_type( $course_id );
		if ( 'paid' === $course_type ) {
			return apply_filters( 'academy/course/is_course_purchasable', true, $course_id );
		}
		return apply_filters( 'academy/course/is_course_purchasable', false, $course_id );
	}

	/**
	 * Formatted price for the plain, engine-independent `academy_course_price`
	 * meta — the price source that drives the course builder's Free/Paid
	 * derivation whenever WooCommerce/EDD isn't the configured monetization
	 * engine (StoreEngine, or no engine at all). The frontend price displays
	 * (`loop/price.php`, `single-course/enroll/pricing.php`) only ever looked
	 * up a WooCommerce/EDD product price — a course priced this way showed no
	 * number at all, just the generic "Paid" text. Used as the last-resort
	 * fallback in those templates, after the engine-specific lookups have had
	 * their turn and come back empty.
	 *
	 * @param int $course_id
	 */
	public static function get_plain_course_price_html( $course_id ) {
		$price = (float) get_post_meta( $course_id, 'academy_course_price', true );
		if ( $price <= 0 ) {
			return '';
		}

		$monetization_engine = self::get_settings( 'monetization_engine' );
		$currency_symbol     = '$';
		if ( 'woocommerce' === $monetization_engine && self::is_active_woocommerce() ) {
			$currency_symbol = html_entity_decode( get_woocommerce_currency_symbol(), ENT_HTML5, 'UTF-8' );
		} elseif ( 'storeengine' === $monetization_engine && self::is_plugin_active( 'storeengine/storeengine.php' ) ) {
			$currency_symbol = \StoreEngine\Utils\Helper::get_currency_symbol();
		}

		return apply_filters(
			'academy/course/plain_price_html',
			'<span class="academy-price-amount">' . esc_html( $currency_symbol . number_format_i18n( $price, 2 ) ) . '</span>',
			$price,
			$course_id
		);
	}

	/**
	 * Formatted price (regular/sale HTML, same shape as
	 * WC_Product::get_price_html()) for a course or bundle sold through
	 * StoreEngine. StoreEngine courses/bundles aren't tied to a
	 * WooCommerce-style product post — their price lives in StoreEngine's own
	 * integration/price tables, looked up by provider + item ID (the same
	 * repository the admin pricing UI reads/writes).
	 *
	 * @param int    $item_id  Course or bundle ID.
	 * @param string $provider StoreEngine integration provider, e.g.
	 *                         'storeengine/academylms' for courses or
	 *                         'storeengine/course-bundle' for bundles.
	 * @return string Price HTML, or '' if StoreEngine has no price for it.
	 */
	public static function get_storeengine_price_html( $item_id, $provider = 'storeengine/academylms' ) {
		if ( ! self::is_plugin_active( 'storeengine/storeengine.php' ) || ! class_exists( \StoreEngine::class ) ) {
			return '';
		}

		$integrations = \StoreEngine\Utils\Helper::get_integration_repository_by_id( $provider, $item_id );
		$integration  = current( $integrations );

		return $integration ? $integration->price->get_price_html() : '';
	}

	/**
	 * Query parts for filtering courses by type (free / paid / public...).
	 *
	 * The saved `academy_course_type` meta is not the whole story: an
	 * integration can change a course's displayed type at runtime (e.g. a
	 * StoreEngine membership makes a "free" course paid, see the
	 * `academy/course/get_course_type` filter). Integrations adjust the parts
	 * through `academy/course/type_query` so the filter matches what the course
	 * card shows.
	 *
	 * @param string[] $types Selected types.
	 * @return array{meta: array[], args: array} `meta` clauses to AND with the
	 *         other meta clauses, `args` to merge into the WP_Query args.
	 */
	public static function course_type_query( $types ) {
		$types = array_values( (array) $types );
		return apply_filters(
			'academy/course/type_query',
			array(
				'meta' => array(
					array(
						'key'     => 'academy_course_type',
						'value'   => $types,
						'compare' => 'IN',
					),
				),
				'args' => array(),
			),
			$types
		);
	}

	/**
	 * Merge the WP_Query args from course_type_query() into existing ones.
	 *
	 * @param array $args  Query args.
	 * @param array $extra Args from course_type_query().
	 * @return array
	 */
	public static function merge_course_type_args( $args, $extra ) {
		foreach ( $extra as $key => $value ) {
			$args[ $key ] = ( isset( $args[ $key ] ) && is_array( $args[ $key ] ) && is_array( $value ) )
				? array_values( array_unique( array_merge( $args[ $key ], $value ) ) )
				: $value;
		}
		return $args;
	}

	public static function get_course_type( $course_id ) {
		$course_type = get_post_meta( $course_id, 'academy_course_type', true );
		return apply_filters( 'academy/course/get_course_type', $course_type, $course_id );
	}

	public static function is_product_in_cart( $product_id ) {
		global $woocommerce;
		if ( $woocommerce->cart ) {
			foreach ( $woocommerce->cart->get_cart() as $key => $val ) {
				if ( (int) $product_id === (int) $val['product_id'] ) {
					return true;
				}
			}
		}
		return false;
	}

	public static function product_belongs_with_course( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM 	{$wpdb->postmeta} WHERE	meta_key = %s  AND meta_value = %d  limit 1",
				'academy_course_product_id',
				$product_id
			)
		);
	}

	public static function download_belongs_with_course( $downlaod_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value = %d LIMIT 1",
				'academy_course_download_id',
				$downlaod_id
			)
		);
	}

	public static function download_belongs_to_course( $download_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM 	{$wpdb->postmeta} WHERE	meta_key = %s  AND meta_value = %d  limit 1",
				'academy_course_download_id',
				$download_id
			)
		);
	}

	public static function is_course_slug_exist( $post_title ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(ID) 
                FROM {$wpdb->posts} 
                WHERE post_title = %s
				AND post_type = %s",
				$post_title, 'academy_courses'
			)
		);
		return $course_exists > 0 ? true : false;
	}

	public static function is_academy_order( $order_id ) {
		return get_post_meta( $order_id, 'is_academy_order_for_course', true );
	}

	public static function get_course_enrolled_ids_by_order_id( $order_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$courses_ids = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key LIKE %s", $order_id, 'academy_order_for_course_id_%' ) );
		if ( is_array( $courses_ids ) && count( $courses_ids ) ) {
			$course_enrolled_by_order = array();
			foreach ( $courses_ids as $courses_id ) {
				$course_id                  = str_replace( 'academy_order_for_course_id_', '', $courses_id->meta_key );
				$course_enrolled_by_order[] = array(
					'course_id'   => $course_id,
					'enrolled_id' => $courses_id->meta_value,
					'order_id'    => $courses_id->post_id,
				);
			}
			return $course_enrolled_by_order;
		}
		return false;
	}

	public static function get_course_preview_video( $id ) {
		$output      = '';
		$intro_video = get_post_meta( $id, 'academy_course_intro_video', true );
		if ( $intro_video && is_array( $intro_video ) && count( $intro_video ) > 1 && ! empty( $intro_video[1] ) ) {
			$type = $intro_video[0];
			if ( 'html5' === $type ) {
				$attachment_id = (int) $intro_video[1];
				$att_url       = wp_get_attachment_url( $attachment_id );
				$thumb_id      = get_post_thumbnail_id( $attachment_id );
				$thumb_url     = wp_get_attachment_url( $thumb_id );
				$output       .= sprintf(
					'<video class="academy-plyr" id="academyPlayer" playsinline controls data-poster="%s">
                <source src="%s" type="video/mp4" />
            </video>',
					$thumb_url,
					$att_url
				);
			} elseif ( 'embedded' === $type ) {
				$embed = Helper::parse_embedded_url( $intro_video[1] );
				$output .= sprintf( '<div class="academy-embed-responsive"><iframe class="academy-embed-responsive-item" src="%s" webkitallowfullscreen mozallowfullscreen allowfullscreen></iframe></div>', esc_url( $embed['url'] ) );
			} elseif ( 'youtube' === $type || 'vimeo' === $type ) {
				$embed = Helper::get_basic_url_to_embed_url( $intro_video[1] );
				$output .= sprintf( '<div class="academy-plyr plyr__video-embed" id="academyPlayer"><iframe src="%s" webkitallowfullscreen mozallowfullscreen allowfullscreen></iframe></div>', esc_url( $embed['url'] ) );
			} elseif ( 'shortcode' === $type ) {
				$output .= Helper::render_video_shortcode( $intro_video[1] );
			} else {
				$embed = Helper::get_basic_url_to_embed_url( $intro_video[1] );
				$output .= sprintf( '<div class="academy-embed-responsive academy-embed-responsive-16by9"><iframe class="academy-embed-responsive-item" src="%s" webkitallowfullscreen mozallowfullscreen allowfullscreen></iframe></div>', esc_url( $embed['url'] ) );
			}//end if
		}//end if
		return $output;
	}

	public static function is_course_fully_booked( $course_id ) {
		$total_enrolled = self::count_course_enrolled( $course_id );
		$max_students   = (int) get_post_meta( $course_id, 'academy_course_max_students', true );
		if ( $max_students ) {
			return $max_students <= $total_enrolled;
		}
		return false;
	}
	public static function get_available_seats( $course_id ): int {
		$total_enrolled = self::count_course_enrolled( $course_id );
		$max_students   = (int) get_post_meta( $course_id, 'academy_course_max_students', true );

		if ( 0 === $max_students ) {
			return PHP_INT_MAX;
		}

		if ( $max_students > $total_enrolled ) {
			return $max_students - $total_enrolled;
		}
		return 0;
	}

	public static function count_course_enrolled( $course_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(ID) 
			FROM	{$wpdb->posts} 
			WHERE 	post_type = %s
					AND post_status = %s
					AND post_parent = %d;
			",
				'academy_enrolled',
				'completed',
				$course_id
			)
		);

		return apply_filters( 'academy/count_course_enrolled', intval( $course_ids ), $course_id );
	}

	public static function get_total_number_of_students() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(enrollment.ID)
			FROM 	{$wpdb->posts} enrollment 
					LEFT  JOIN {$wpdb->posts} course
							ON enrollment.post_parent=course.ID
			WHERE 	course.post_type = %s
					AND course.post_status = %s
					AND enrollment.post_type = %s
					AND enrollment.post_status = %s;
			",
				'academy_courses',
				'publish',
				'academy_enrolled',
				'completed'
			)
		);
		return (int) $count;
	}

	public static function get_total_number_of_students_by_instructor( $instructor_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT enrollment.post_author)
			FROM 	{$wpdb->posts} enrollment 
					LEFT  JOIN {$wpdb->posts} course
							ON enrollment.post_parent=course.ID
			WHERE 	course.post_author = %d
                    AND course.post_type = %s
					AND course.post_status = %s
					AND enrollment.post_type = %s
					AND enrollment.post_status = %s;
			",
				$instructor_id,
				'academy_courses',
				'publish',
				'academy_enrolled',
				'completed'
			)
		);
		return (int) $count;
	}

	public static function get_total_students_by_instructor( $instructor_id ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom query with no WP API equivalent
		$student_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT enrollment.post_author
				FROM {$wpdb->posts} enrollment
				LEFT JOIN {$wpdb->posts} course
					ON enrollment.post_parent = course.ID
				LEFT JOIN {$wpdb->usermeta} um
					ON um.meta_key = %s
					AND um.user_id = %d
					AND um.meta_value = course.ID
				WHERE (course.post_author = %d OR um.user_id = %d)
					AND course.post_type = %s
					AND course.post_status = %s
					AND enrollment.post_type = %s",
				'academy_instructor_course_id',
				$instructor_id,
				$instructor_id,
				$instructor_id,
				'academy_courses',
				'publish',
				'academy_enrolled'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		return $student_ids;
	}

	public static function get_reviews_by_user( $user_id, $offset = 0, $limit = 150 ) {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users -- reporting JOIN on users/usermeta that get_users() cannot express
		$reviews = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$wpdb->comments}.comment_ID,
					{$wpdb->comments}.comment_post_ID,
					{$wpdb->comments}.comment_author,
					{$wpdb->comments}.comment_author_email,
					{$wpdb->comments}.comment_date,
					{$wpdb->comments}.comment_content,
					{$wpdb->comments}.user_id,
					{$wpdb->commentmeta}.meta_value as rating,
					{$wpdb->users}.display_name
			
			FROM 	{$wpdb->comments}
					INNER JOIN {$wpdb->commentmeta} 
							ON {$wpdb->comments}.comment_ID = {$wpdb->commentmeta}.comment_id 
					INNER  JOIN {$wpdb->users}
							ON {$wpdb->comments}.user_id = {$wpdb->users}.ID
			WHERE 	{$wpdb->comments}.user_id = %d 
					AND comment_type = %s
					AND meta_key = %s
			ORDER BY comment_ID DESC
			LIMIT %d, %d;
			",
				$user_id,
				'academy_courses',
				'academy_rating',
				$offset,
				$limit
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users
		return $reviews;
	}

	public static function get_reviews_by_instructor( $instructor_id, $offset = 0, $limit = 150 ) {
		global $wpdb;
		$results    = array();
		$course_ids = (array) self::get_assigned_courses_ids_by_instructor_id( $instructor_id );
		if ( count( $course_ids ) ) {
			$implode_ids_placeholder = implode( ', ', array_fill( 0, count( $course_ids ), '%d' ) );
			$prepare_values           = array_merge( $course_ids, array(
				'academy_courses',
				'academy_rating',
				$offset,
				$limit,
			));
			// phpcs:disable
			$results     = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT {$wpdb->comments}.comment_ID, 
						{$wpdb->comments}.comment_post_ID, 
						{$wpdb->comments}.comment_author, 
						{$wpdb->comments}.comment_author_email, 
						{$wpdb->comments}.comment_date, 
						{$wpdb->comments}.comment_content, 
						{$wpdb->comments}.user_id, 
						{$wpdb->commentmeta}.meta_value AS rating,
						{$wpdb->users}.display_name 
			
				FROM 	{$wpdb->comments}
						INNER JOIN {$wpdb->commentmeta} 
								ON {$wpdb->comments}.comment_ID = {$wpdb->commentmeta}.comment_id 
						INNER JOIN {$wpdb->users}
								ON {$wpdb->comments}.user_id = {$wpdb->users}.ID
				WHERE 	{$wpdb->comments}.comment_post_ID IN($implode_ids_placeholder) 
						AND comment_type = %s
						AND meta_key = %s
				ORDER BY comment_ID DESC
				LIMIT %d, %d;
				",
					$prepare_values
				)
			);
			// phpcs:enable
		}//end if
		return (array) $results;
	}

	public static function get_review_by_user( $user_id, $course_id ) {
		return get_comments( array(
			'user_id'      => $user_id,
			'post_id'      => $course_id,
			'comment_type' => 'academy_courses',
		) );
	}

	public static function get_assigned_courses_ids_by_instructor_id( $user_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT 	meta_value
			FROM		{$wpdb->usermeta}
			WHERE 		meta_key = %s
						AND user_id = %d
			GROUP BY 	meta_value;
			",
				'academy_instructor_course_id',
				$user_id
			)
		);
		return $course_ids;
	}

	public static function get_orders_by_user_id( $user_id ) {
		global $wpdb;
		$orders = [];

		if ( 'yes' === get_option( 'woocommerce_custom_orders_table_enabled' ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$orders = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						orders.id AS ID,
						orders.customer_id AS post_author,
						orders.date_created_gmt AS post_date_gmt,
						orders.date_created_gmt AS post_date,
						orders.status AS post_status,
						orders.date_updated_gmt AS post_modified_gmt
					FROM {$wpdb->prefix}wc_orders AS orders
					INNER JOIN {$wpdb->prefix}postmeta AS meta
					ON orders.id = meta.post_id
						AND meta.meta_key = 'is_academy_order_for_course'
					WHERE orders.customer_id = %d",
					$user_id
				)
			);
		} else {
			$post_type = 'shop_order';
			$user_meta = '_customer_user';
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$orders    = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT {$wpdb->posts}.*
				FROM	{$wpdb->posts}
						INNER JOIN {$wpdb->postmeta} customer
							ON id = customer.post_id
							AND customer.meta_key = %s
						INNER JOIN {$wpdb->postmeta} academy_order
							ON id = academy_order.post_id
							AND academy_order.meta_key = 'is_academy_order_for_course'
				WHERE	post_type = %s
						AND customer.meta_value = %d 
				ORDER BY {$wpdb->posts}.id DESC",
					$user_meta,
					$post_type,
					$user_id
				)
			);
		}//end if
		return $orders;
	}

	public static function order_status_context( $status = null ) {
		$status      = str_replace( 'wc-', '', $status );
		$status_name = ucwords( str_replace( '-', ' ', $status ) );
		return "<span class='label-order-status label-status-{$status}'>$status_name</span>";
	}

	public static function get_total_number_of_course_lesson( $ID ) {
		$total_lessons = 0;
		$total_file = 0;
		$curriculum    = get_post_meta( $ID, 'academy_course_curriculum', true );
		if ( is_array( $curriculum ) ) {
			if ( is_array( $curriculum ) && count( $curriculum ) ) {
				foreach ( $curriculum as $topic ) {
					if ( isset( $topic['topics'] ) && is_array( $topic['topics'] ) && count( $topic['topics'] ) ) {
						foreach ( $topic['topics'] as $topic_item ) {
							$type = isset( $topic_item['type'] ) ? $topic_item['type'] : '';
							if ( empty( $type ) ) {
								continue;
							}

							if ( $type && 'lesson' === $type ) {
								// phpcs:ignore Squiz.Operators.IncrementDecrementUsage.Found
								$total_lessons += 1;
								$total_file += (int) \Academy\Helper::get_lesson_meta( $topic_item['id'], 'featured_media' );
							}
							if ( 'sub-curriculum' === $type ) {
								foreach ( $topic_item['topics'] as $sub_topic ) {
									if ( isset( $sub_topic['type'] ) && 'lesson' === $sub_topic['type'] ) {
										// phpcs:ignore Squiz.Operators.IncrementDecrementUsage.Found
										$total_lessons += 1;
										$total_file += (int) \Academy\Helper::get_lesson_meta( $topic_item['id'], 'featured_media' );
									}
								}
							}
						}//end foreach
					}//end if
				}//end foreach
			}//end if
		}//end if
		return $total_lessons;
	}

	public static function get_total_number_of_course_lesson_resource( $course_id ) {
		$curriculum = get_post_meta( $course_id, 'academy_course_curriculum', true );

		if ( empty( $curriculum ) || ! is_array( $curriculum ) ) {
			return 0;
		}

		$total = 0;

		foreach ( $curriculum as $topic ) {
			if ( empty( $topic['topics'] ) ) {
				continue;
			}

			foreach ( $topic['topics'] as $item ) {
				if ( empty( $item['type'] ) ) {
					continue;
				}

				if ( 'lesson' === $item['type'] && ! empty( $item['id'] ) ) {
					if ( \Academy\Helper::get_lesson_meta( $item['id'], 'attachment' ) ) {
						++$total;
					}
				}

				// Nested sub-curriculum lessons
				if ( 'sub-curriculum' === $item['type'] && ! empty( $item['topics'] ) ) {
					foreach ( $item['topics'] as $sub_item ) {
						if (
							! empty( $sub_item['type'] ) &&
							'lesson' === $sub_item['type'] &&
							! empty( $sub_item['id'] ) &&
							\Academy\Helper::get_lesson_meta( $sub_item['id'], 'attachment' )
						) {
							++$total;
						}
					}
				}
			}//end foreach
		}//end foreach

		return $total;
	}

	public static function get_course_duration( $ID ) {
		$duration = get_post_meta( $ID, 'academy_course_duration', true );
		if ( is_array( $duration ) && count( $duration ) && ( $duration[0] || $duration[1] || $duration[2] ) ) {
			$hours   = isset( $duration[0] ) ? intval( $duration[0] ) : 0;
			$minutes = isset( $duration[1] ) ? intval( $duration[1] ) : 0;
			$seconds = isset( $duration[2] ) ? intval( $duration[2] ) : 0;

			$parts = [];

			if ( $hours > 0 ) {
				$parts[] = $hours . ' hr';
			}

			if ( $minutes > 0 ) {
				$parts[] = $minutes . ' mins';
			}

			if ( $seconds > 0 ) {
				$parts[] = $seconds . ' sec';
			}

			return implode( ' ', $parts );
		}//end if
		return '';
	}

	public static function get_instructors_by_course_id( $course_id, $offset = 0, $per_page = 10 ) {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users -- reporting JOIN on users/usermeta that get_users() cannot express
		$query = $wpdb->prepare(
			"SELECT 
				u.ID,
				u.display_name,
				get_course.meta_value AS academy_course_id,
				designation_meta.meta_value AS academy_profile_designation,
				bio_meta.meta_value AS academy_profile_bio
			 FROM {$wpdb->users} u
			 INNER JOIN {$wpdb->usermeta} get_course 
				ON u.ID = get_course.user_id
			   AND get_course.meta_key = %s
			   AND get_course.meta_value = %d
			 LEFT JOIN {$wpdb->usermeta} designation_meta 
				ON u.ID = designation_meta.user_id
			   AND designation_meta.meta_key = %s
			 LEFT JOIN {$wpdb->usermeta} bio_meta 
				ON u.ID = bio_meta.user_id
			   AND bio_meta.meta_key = %s
			 LIMIT %d, %d",
			'academy_instructor_course_id',
			$course_id,
			'academy_profile_designation',
			'academy_profile_bio',
			$offset,
			$per_page
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom query with no WP API equivalent
		$instructors = $wpdb->get_results( $query );//phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		return ! empty( $instructors ) ? $instructors : [];
	}

	public static function get_instructor_by_author_id( $author_id ) {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users -- reporting JOIN on users/usermeta that get_users() cannot express
		$instructors = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ID,
					display_name,
					academy_profile_designation.meta_value AS academy_profile_designation,
					academy_profile_bio.meta_value AS academy_profile_bio
			FROM	{$wpdb->users} 
					LEFT  JOIN {$wpdb->usermeta} academy_profile_designation 
						ON ID = academy_profile_designation.user_id 
						AND academy_profile_designation.meta_key = %s 
					LEFT  JOIN {$wpdb->usermeta} academy_profile_bio 
						ON ID = academy_profile_bio.user_id 
						AND academy_profile_bio.meta_key = %s 
			WHERE ID = %d
			",
				'academy_profile_designation',
				'academy_profile_bio',
				$author_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users

		if ( count( $instructors ) ) {
			return $instructors;
		}
		return false;
	}

	public static function get_course_ids_by_instructor_id( $instructor_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT meta_value
			FROM   {$wpdb->usermeta}
			WHERE  user_id = %d
				AND meta_key = 'academy_instructor_course_id'
			",
				$instructor_id
			)
		);
		if ( count( $course_ids ) ) {
			return $course_ids;
		}
		return false;
	}

	public static function get_user_id_from_course_id( $course_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_var( $wpdb->prepare( "SELECT post_author FROM {$wpdb->posts} WHERE ID = %d ", $course_id ) );
		return $results;
	}

	public static function get_order_status_by_id( $order_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_var( $wpdb->prepare( "SELECT post_status from {$wpdb->posts} where ID = %d ", $order_id ) );
		return $results;
	}

	public static function prepare_course_search_query_args( $data ) {
		$defaults = array(
			'search'         => '',
			'category'       => [],
			'tags'           => [],
			'levels'         => [],
			'type'           => [],
			'paged'          => 1,
			'posts_per_page' => 12,
		);
		$data     = wp_parse_args( $data, $defaults );

		// base
		$args = array(
			'post_type'      => apply_filters( 'academy/get_course_archive_post_types', array( 'academy_courses' ) ),
			'post_status'    => 'publish',
			'posts_per_page' => $data['posts_per_page'],
			'paged'          => $data['paged'],
		);

		// taxonomy
		$tax_query = array();
		if ( count( $data['category'] ) > 0 ) {
			$tax_query[] = array(
				'taxonomy' => 'academy_courses_category',
				'field'    => 'slug',
				'terms'    => $data['category'],
			);
		}
		if ( count( $data['tags'] ) > 0 ) {
			$tax_query[] = array(
				'taxonomy' => 'academy_courses_tag',
				'field'    => 'slug',
				'terms'    => $data['tags'],
			);
		}
		if ( count( $tax_query ) > 0 ) {
			$tax_query['relation'] = 'AND';
			// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_tax_query
			$args['tax_query']     = $tax_query;
		}
		// meta
		$meta_query = array();
		$type_query = array( 'args' => array() );
		if ( count( $data['levels'] ) > 0 ) {
			$meta_query[] = array(
				'key'     => 'academy_course_difficulty_level',
				'value'   => $data['levels'],
				'compare' => 'IN',
			);
		}
		if ( count( $data['type'] ) > 0 ) {
			$type_query = self::course_type_query( $data['type'] );
			$meta_query = array_merge( $meta_query, $type_query['meta'] );
		}
		if ( count( $meta_query ) > 0 ) {
			$tax_query['relation'] = 'AND';
			// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
			$args['meta_query']    = $meta_query;
		}
		if ( ! empty( $type_query['args'] ) ) {
			$args = self::merge_course_type_args( $args, $type_query['args'] );
		}

		// search
		if ( ! empty( $data['search'] ) ) {
			$args['s'] = $data['search'];
		}

		// order by
		if ( isset( $data['orderby'] ) ) {
			switch ( $data['orderby'] ) {
				case 'name':
					$args['orderby'] = 'post_title';
					$args['order']   = 'asc';
					break;
				case 'date':
					$args['orderby'] = 'date';
					$args['order']   = 'desc';
					break;
				case 'modified':
					$args['orderby'] = 'modified';
					$args['order']   = 'desc';
					break;
				case 'ratings':
					$args['orderby'] = 'comment_count';
					$args['order']   = 'desc';
					break;
				case 'menu_order':
					$args['orderby'] = 'menu_order';
					$args['order']   = 'desc';
					break;
				default:
					$args['orderby'] = 'ID';
					$args['order']   = 'desc';
			}//end switch
		}//end if

		$order_field = isset( $args['orderby'] ) ? $args['orderby'] : 'ID';
		$order_dir   = isset( $args['order'] ) ? $args['order'] : 'desc';

		// `orderby` becomes an array, which carries its own per-key direction —
		// a lone `order` alongside it is ignored by WP_Query.
		$args = array_merge( $args, self::apply_sticky_course_ordering( $order_field, $order_dir ) );
		unset( $args['order'] );

		return apply_filters( 'academy/get_course_archive_search_query_args', $args, $data );
	}

	public static function get_store_orders_by_user_id( int $user_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$orders = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					orders.id AS ID,
					orders.customer_id AS post_author,
					orders.date_created_gmt AS post_date_gmt,
					orders.date_created_gmt AS post_date,
					orders.status AS post_status,
					orders.date_updated_gmt AS post_modified_gmt,
					orders.total_amount AS amount,
					orders.currency AS currency,
					meta.post_id AS course_id
				FROM {$wpdb->prefix}storeengine_orders AS orders
				INNER JOIN {$wpdb->prefix}postmeta AS meta
				ON orders.id = meta.meta_value
					AND meta.meta_key = 'is_academy_store_order_id'
				WHERE orders.customer_id = %d",
				$user_id
			)
		);
		return $orders;
	}

	public static function get_sample_permalink_args( $id, $new_title = null, $new_slug = null ) {
		$response = array();
		$post = get_post( $id );
		if ( ! $post ) {
			return '';
		}
		list($permalink, $post_name) = get_sample_permalink( $post->ID, $new_title, $new_slug );
		$view_link      = false;
		if ( current_user_can( 'read_post', $post->ID ) ) {
			if ( 'draft' === $post->post_status || empty( $post->post_name ) ) {
				$view_link      = get_preview_post_link( $post );
			} elseif ( 'publish' === $post->post_status || 'attachment' === $post->post_type ) {
					$view_link = get_permalink( $post );
			} else {
				// Allow non-published (private, future) to be viewed at a pretty permalink, in case $post->post_name is set.
				$view_link = str_replace( array( '%pagename%', '%postname%' ), $post->post_name, $permalink );
			}
		}
		if ( false !== $view_link ) {
			$response['view_link'] = esc_url( $view_link );
		} else {
			$response['permalink'] = $permalink;
		}

		$display_link   = rtrim( str_replace( '%pagename%', $post_name, $permalink ) );
		$response['editable_postname'] = $post_name;
		$response['display_link'] = $display_link;
		$response['post_name'] = $post_name;

		return (array) $response;
	}

	public static function get_last_course_id() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_col( "
			SELECT MAX(ID) FROM {$wpdb->prefix}posts
			WHERE post_type LIKE 'academy_courses'
			AND post_status = 'publish'"
		);
		return reset( $results );
	}

	public static function cancel_course_enroll( $course_id, $user_id ) {
		if ( empty( $course_id ) || empty( $user_id ) ) {
			return;
		}

		$enrolled  = self::is_enrolled( $course_id, $user_id );
		if ( $enrolled ) {
			global $wpdb;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->delete(
				$wpdb->posts,
				array(
					'post_type'   => 'academy_enrolled',
					'post_author' => $user_id,
					'post_parent' => $course_id
				)
			);

			$order_id = get_post_meta( $enrolled->ID, 'academy_enrolled_by_order_id', true );
			delete_post_meta( $enrolled->ID, 'academy_enrolled_by_order_id' );
			delete_post_meta( $enrolled->ID, 'academy_enrolled_by_product_id' );
			delete_post_meta( $order_id, 'is_academy_order_for_course' );
			delete_post_meta( $order_id, 'academy_order_for_course_id_' . $course_id );
			delete_user_meta( $user_id, 'academy_course_' . $course_id . '_completed_topics' );
		}
	}

	public static function delete_enrolled_courses( $course_id ) {
		if ( empty( $course_id ) ) {
			return;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$enrolled_courses = $wpdb->get_results(
			$wpdb->prepare( "SELECT ID, post_author FROM {$wpdb->posts} WHERE post_type=%s AND post_parent=%d", 'academy_enrolled', $course_id )
		);
		if ( is_array( $enrolled_courses ) && count( $enrolled_courses ) ) {
			foreach ( $enrolled_courses as $enrolled_course ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->delete(
					$wpdb->posts,
					array(
						'post_type'   => 'academy_enrolled',
						'ID' => $enrolled_course->ID,
					)
				);
				$order_id = get_post_meta( $enrolled_course->ID, 'academy_enrolled_by_order_id', true );
				delete_post_meta( $enrolled_course->ID, 'academy_enrolled_by_order_id' );
				delete_post_meta( $enrolled_course->ID, 'academy_enrolled_by_product_id' );
				delete_post_meta( $order_id, 'is_academy_order_for_course' );
				delete_post_meta( $order_id, 'academy_order_for_course_id_' . $course_id );
				delete_user_meta( $enrolled_course->post_author, 'academy_course_' . $course_id . '_completed_topics' );
			}
		}
	}

	public static function update_enroll_status_by_course_ids( $status, $enroll_ids ) {
		global $wpdb;
		$enroll_ids = array_values( array_filter( array_map( 'absint', (array) $enroll_ids ) ) );
		if ( empty( $enroll_ids ) ) {
			return 0;
		}
		$status       = 'complete' === $status ? 'completed' : $status;
		$placeholders = implode( ', ', array_fill( 0, count( $enroll_ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- placeholders only.
		$update       = $wpdb->query(
			$wpdb->prepare( "UPDATE {$wpdb->posts} SET post_status = %s WHERE ID IN ( {$placeholders} )", array_merge( array( $status ), $enroll_ids ) )
		);
		return $update;
	}

	public static function course_has_previewable_lesson( $course_id ) {
		// Preview is a gated feature — only counts when the course-preview addon is active.
		if ( ! (bool) self::get_addon_active_status( 'course-preview' ) ) {
			return false;
		}

		$curriculums = get_post_meta( $course_id, 'academy_course_curriculum', true );
		if ( ! is_array( $curriculums ) ) {
			return false;
		}

		foreach ( $curriculums as $curriculum ) {
			if ( ! isset( $curriculum['topics'] ) || ! is_array( $curriculum['topics'] ) ) {
				continue;
			}
			foreach ( $curriculum['topics'] as $topic ) {
				if ( isset( $topic['type'], $topic['id'] )
					&& 'lesson' === $topic['type']
					&& self::get_lesson_meta( $topic['id'], 'is_previewable' ) ) {
					return true;
				}
				// Nested (sub-curriculum) lessons.
				if ( isset( $topic['topics'] ) && is_array( $topic['topics'] ) ) {
					foreach ( $topic['topics'] as $child_topic ) {
						if ( isset( $child_topic['type'], $child_topic['id'] )
							&& 'lesson' === $child_topic['type']
							&& self::get_lesson_meta( $child_topic['id'], 'is_previewable' ) ) {
							return true;
						}
					}
				}
			}
		}//end foreach

		return false;
	}

	public static function get_course_curriculum( $course_id, $complete_status = true ) {
		$user_id            = get_current_user_id();
		$is_public          = Helper::is_public_course( $course_id );
		$is_enrolled         = Helper::is_enrolled( $course_id, $user_id );
		$curriculums = get_post_meta( $course_id, 'academy_course_curriculum', true );
		$completed_topics = $complete_status ? json_decode( get_user_meta( $user_id, 'academy_course_' . $course_id . '_completed_topics', true ), true ) : [];

		$results    = [];
		if ( is_array( $curriculums ) ) {
			foreach ( $curriculums as $curriculum ) {
				$temp_topic = [];
				if ( is_array( $curriculum['topics'] ) ) {
					foreach ( $curriculum['topics'] as $topic ) {
						if ( ! isset( $topic['type'] ) || empty( $topic['type'] ) ) {
							continue;
						}

						if ( 'quiz' === $topic['type'] && ! Helper::get_addon_active_status( 'quizzes' ) ) {
							continue;
						}
						if ( 'assignment' === $topic['type'] && ! \Academy\Helper::get_addon_active_status( 'assignments', true ) ) {
							continue;
						}
						if ( 'meeting' === $topic['type'] && ! \Academy\Helper::get_addon_active_status( 'meeting', true ) ) {
							continue;
						}
						if ( 'booking' === $topic['type'] && ! \Academy\Helper::get_addon_active_status( 'tutor-booking', true ) ) {
							continue;
						}
						if ( 'quizpress_quiz' === $topic['type'] && ! \Academy\Helper::is_active_quizpress() ) {
							continue;
						}

						$topic['is_completed'] = false;
						$topic['is_accessible'] = false;
						$topic_id = isset( $topic['id'] ) ? $topic['id'] : 0;
						// if topic type is lesson then set duration
						if ( isset( $topic['type'] ) && 'lesson' === $topic['type'] ) {
							$topic['slug']              = \Academy\Helper::get_lesson_slug( $topic_id );
							$topic['duration']          = \Academy\Helper::get_lesson_video_duration( $topic_id );
							$topic['is_accessible']     = \Academy\Helper::get_lesson_meta( $topic_id, 'is_previewable' ) && (bool) \Academy\Helper::get_addon_active_status( 'course-preview' );
						} else {
							$topic['slug'] = basename( get_permalink( $topic_id ) );
						}

						if ( $complete_status ) {
							$topic['is_completed'] = ( isset( $completed_topics[ $topic['type'] ][ $topic_id ] ) ? $completed_topics[ $topic['type'] ][ $topic_id ] : '' );
						}

						if ( $is_public || $is_enrolled ) {
							$topic['is_accessible'] = true;
						}

						// Inner Item
						if ( isset( $topic['topics'] ) && is_array( $topic['topics'] ) ) {
							$temp_child_topic = [];
							foreach ( $topic['topics'] as $child_topic ) {
								if ( 'quiz' === $child_topic['type'] && ! Helper::get_addon_active_status( 'quizzes' ) ) {
									continue;
								}
								if ( 'assignment' === $child_topic['type'] && ! Helper::get_addon_active_status( 'assignments' ) ) {
									continue;
								}
								if ( 'meeting' === $child_topic['type'] && ! Helper::get_addon_active_status( 'meeting' ) ) {
									continue;
								}
								if ( 'booking' === $child_topic['type'] && ! \Academy\Helper::get_addon_active_status( 'tutor-booking', true ) ) {
									continue;
								}
								if ( 'quizpress_quiz' === $child_topic['type'] && ! \Academy\Helper::is_active_quizpress() ) {
									continue;
								}

								$child_topic['is_completed'] = false;

								// if topic type is lesson then set duration
								if ( 'lesson' === $child_topic['type'] ) {
									$child_topic['duration']            = Helper::get_lesson_video_duration( $child_topic['id'] );
									$child_topic['is_accessible']       = \Academy\Helper::get_lesson_meta( $child_topic['id'], 'is_previewable' );
									$child_topic['slug']                = \Academy\Helper::get_lesson_slug( $child_topic['id'] );
								} else {
									$child_topic['slug'] = basename( get_permalink( $child_topic['id'] ) );
								}
								if ( $complete_status ) {
									$child_topic['is_completed'] = ( isset( $completed_topics[ $child_topic['type'] ][ $child_topic['id'] ] ) ? $completed_topics[ $child_topic['type'] ][ $child_topic['id'] ] : '' );
								}

								if ( $is_public || $is_enrolled ) {
									$child_topic['is_accessible'] = true;
								}

								$temp_child_topic[] = $child_topic;

							}//end foreach
							$topic['topics'] = $temp_child_topic;
						}//end if

						$temp_topic[]              = $topic;
					}//end foreach
				}//end if
				$curriculum['topics'] = $temp_topic;
				$results[]           = $curriculum;
			}//end foreach
		}//end if

		return apply_filters( 'academy/get_course_curriculums', $results, $course_id );
	}

	public static function is_public_course( $course_id ) {
		$course_type = self::get_course_type( $course_id );
		return apply_filters( 'academy/is_public_course', false, $course_type );
	}

	public static function is_favorite_course( $course_id ) {
		global $wpdb;
		$user_id = get_current_user_ID();
		$has_data = $wpdb->get_row( $wpdb->prepare( "SELECT * from {$wpdb->usermeta} WHERE user_id = %d AND meta_key = 'academy_course_favorite' AND meta_value = %d;", $user_id, $course_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom query with no WP API equivalent
		if ( $has_data ) {
			return true;
		}
		return false;
	}

	public static function get_topic_icon_class_name( $type ) {
		$icon_class = 'academy-icon academy-icon--';
		switch ( $type ) {
			case 'quiz':
				$icon_class .= 'quiz-alt';
				break;

			case 'assignment':
				$icon_class .= 'assignment';
				break;

			case 'meeting':
				$icon_class .= 'video';
				break;

			case 'booking':
				$icon_class .= 'add';
				break;

			case 'quizpress_quiz':
				$icon_class .= 'quiz-alt';
				break;

			default:
				$icon_class .= 'lesson-alt';
				break;
		}//end switch
		return $icon_class;
	}

	public static function get_course_difficulty_level( $course_id ) {
		$lavel = get_post_meta( $course_id, 'academy_course_difficulty_level', true );
		$levels = apply_filters('academy/difficulty_level', array(
			'all_levels'  => __( 'All Levels', 'academy' ),
			'beginner'    => __( 'Beginner', 'academy' ),
			'intermediate' => __( 'Intermediate', 'academy' ),
			'experts'     => __( 'Experts', 'academy' ),
		));

		if ( isset( $levels[ $lavel ] ) ) {
			return $levels[ $lavel ];
		}

		return '';
	}

	public static function get_the_course_thumbnail_url( $size = 'post-thumbnail' ) {
		$post_id           = get_the_ID();
		$post_thumbnail_id = (int) get_post_thumbnail_id( $post_id );
		if ( $post_thumbnail_id ) {
			$size = apply_filters( 'academy/course_thumbnail_size', $size, $post_id );
			return wp_get_attachment_image_url( $post_thumbnail_id, $size );
		}
		return ACADEMY_ASSETS_URI . '/images/thumbnail-placeholder.png';
	}

	public static function get_the_course_thumbnail_url_by_id( $course_id, $size = 'post-thumbnail' ) {
		$course_thumbnail_url = get_the_post_thumbnail_url( $course_id, $size );
		if ( $course_thumbnail_url ) {
			return $course_thumbnail_url;
		}
		return ACADEMY_ASSETS_URI . '/images/thumbnail-placeholder.png';
	}

	public static function get_the_course_category_image_url( $term_id, $size = 'thumbnail' ) {
		$image_id = get_term_meta( $term_id, 'academy_category_image', true );
		if ( $image_id ) {
			$image_url = wp_get_attachment_image_url( $image_id, $size );
			if ( $image_url ) {
				return $image_url;
			}
		}
		return ACADEMY_ASSETS_URI . '/images/thumbnail-placeholder.png';
	}

	public static function prepare_category_results( $terms, $parent_id = 0 ) {
		$category = array();
		foreach ( $terms as $term ) {
			if ( $term->parent === $parent_id ) {
				$term->children = self::prepare_category_results( $terms, $term->term_id );
				$category[]     = $term;
			}
		}
		return $category;
	}

	public static function get_all_courses_category_lists() {
		$categories = get_terms(
			array(
				'taxonomy'   => 'academy_courses_category',
				'hide_empty' => true,
			)
		);
		return self::prepare_category_results( $categories );
	}

	public static function get_topic_play_link( $topic, $course_id = '' ) {
		$type = isset( $topic['type'] ) ? $topic['type'] : '';
		$id = isset( $topic['id'] ) ? $topic['id'] : '';
		$slug = isset( $topic['slug'] ) ? $topic['slug'] :
			( self::get_topics_post_slug( $id, $type ) ?? '' );
		if ( \Academy\Helper::is_server_learn_page() ) {
			$permalinks = \Academy\Helper::get_permalink_structure();
			if ( empty( $course_id ) ) {
				$course_id = ( false === \Academy\Helper::get_the_current_course_id() ) ? get_the_ID() : \Academy\Helper::get_the_current_course_id();
			}
			$course_name = get_post( $course_id )->post_name;
			$course_rewrite_slug = str_replace( '/', '', $permalinks['course_rewrite_slug'] );
			$url = home_url( "/{$course_rewrite_slug}/{$course_name}/{$type}/{$slug}" );
			return add_query_arg( array(), $url );
		}
		return add_query_arg( array( 'source' => "curriculums#/$type/$id" ), get_the_permalink( $course_id ) );
	}

	public static function get_topics_post_slug( $id, $type ) {
		switch ( $type ) {
			case 'lesson':
				return Helper::get_lesson_slug( $id );
			case 'quiz':
				return get_post( $id )->post_name;
			case 'assignment':
				return get_post( $id )->post_name;
			case 'meeting':
				return get_post( $id )->post_name;
			case 'booking':
				return get_post( $id )->post_name;
			case 'quizpress_quiz':
				return get_post( $id )->post_name;
		}
	}

	public static function get_course_curriculums_number_of_counts( $course_id ) {
		$curriculum_counts = [
			'total_lessons' => 0,
			'total_assignments' => 0,
			'total_quizzes' => 0,
			'total_zoom_meetings' => 0,
			'total_tutor_bookings' => 0,
			'total_quizpress_quizzes' => 0,
		];

		$course_topic = get_post_meta( $course_id, 'academy_course_curriculum', true );

		if ( is_array( $course_topic ) ) {
			foreach ( $course_topic as $topic ) {
				if ( isset( $topic['topics'] ) && is_array( $topic['topics'] ) ) {
					foreach ( $topic['topics'] as $lesson ) {
						if ( isset( $lesson['type'] ) ) {
							switch ( $lesson['type'] ) {
								case 'lesson':
									++$curriculum_counts['total_lessons'];
									break;
								case 'assignment':
									++$curriculum_counts['total_assignments'];
									break;
								case 'quiz':
									++$curriculum_counts['total_quizzes'];
									break;
								case 'meeting':
									++$curriculum_counts['total_zoom_meetings'];
									break;
								case 'booking':
									++$curriculum_counts['total_tutor_bookings'];
									break;
								case 'quizpress_quiz':
									++$curriculum_counts['total_quizpress_quizzes'];
									break;
								case 'sub-curriculum':
									foreach ( $lesson['topics'] as $sub_topic ) {
										switch ( $sub_topic['type'] ) {
											case 'lesson':
												++$curriculum_counts['total_lessons'];
												break;
											case 'assignment':
												++$curriculum_counts['total_assignments'];
												break;
											case 'quiz':
												++$curriculum_counts['total_quizzes'];
												break;
											case 'meeting':
												++$curriculum_counts['total_zoom_meetings'];
												break;
											case 'booking':
												++$curriculum_counts['total_tutor_bookings'];
												break;
											case 'quizpress_quiz':
												++$curriculum_counts['total_quizpress_quizzes'];
												break;
											default:
												break;
										}//end switch
									}//end foreach
								default:
									break;
							}//end switch
						}//end if
					}//end foreach
				}//end if
			}//end foreach
		}//end if
		$curriculum_counts['total_topics'] = apply_filters(
			'academy/count_total_topics_in_curriculums',
			array_sum( $curriculum_counts ),
			$course_id
		);
		return $curriculum_counts;
	}

	public static function get_total_topic_title_by_course_id( $course_id ) {
		$curriculums = self::get_course_curriculums( $course_id );
		if ( empty( $curriculums ) ) {
			return __( 'No topics list found', 'academy' );
		}
		$topics_title = array_column( $curriculums, 'name' );
		$string = '';
		foreach ( $topics_title as $key => $item ) {
			$string .= ( $key + 1 ) . '. ' . $item . ' ';
		}

		return $string;
	}

	public static function get_course_curriculums( $course_id ) {
		$course_curriculum = get_post_meta( $course_id, 'academy_course_curriculum', true );
		$prepare_curriculum = array();

		if ( empty( $course_curriculum ) || ! is_array( $course_curriculum ) ) {
			return $prepare_curriculum;
		}

		foreach ( $course_curriculum as $curriculum ) {
			if ( empty( $curriculum['topics'] ) || ! is_array( $curriculum['topics'] ) ) {
				continue;
			}

			foreach ( $curriculum['topics'] as $topic ) {
				if ( ! empty( $topic['topics'] ) && is_array( $topic['topics'] ) ) {
					$prepare_curriculum = array_merge( $prepare_curriculum, $topic['topics'] );
				} else {
					$prepare_curriculum[] = $topic;
				}
			}
		}

		return $prepare_curriculum;
	}

	/**
	 * Whether $lesson_id is a lesson listed in the curriculum of $course_id.
	 *
	 * @param int $lesson_id Lesson ID.
	 * @param int $course_id Course ID.
	 * @return bool
	 */
	public static function is_lesson_in_course( $lesson_id, $course_id ) {
		$lesson_id = (int) $lesson_id;
		if ( ! $lesson_id || ! $course_id ) {
			return false;
		}
		foreach ( self::get_course_curriculums( $course_id ) as $topic ) {
			if ( isset( $topic['id'], $topic['type'] ) && 'lesson' === $topic['type'] && (int) $topic['id'] === $lesson_id ) {
				return true;
			}
		}
		return false;
	}

	public static function get_course_download_id( $course_id ) {
		$download_id = (int) get_post_meta( $course_id, 'academy_course_download_id', true );
		return apply_filters( 'academy/course/get_course_download_id', $download_id, $course_id );
	}
	public static function get_the_current_course_id() {
		$course_name = get_query_var( 'course_name' );
		$args = apply_filters( 'academy/get_courses_args', array(
			'name'        => $course_name,
			'post_type'   => 'academy_courses',
			'post_status' => 'any',
			'numberposts' => 1
		) );
		$course = current( get_posts( $args ) );
		if ( $course ) {
			return $course->ID;
		}
		return false;
	}

	public static function get_course_announcements_by_course_id( $course_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$announcement_ids = $wpdb->get_col($wpdb->prepare(
			"SELECT post_id 
				FROM {$wpdb->postmeta} 
					WHERE meta_key = %s 
					AND meta_value LIKE %s",
			'academy_announcements_course_ids',
			'%"value";i:' . $course_id . ';%'
		));

		if ( ! empty( $announcement_ids ) ) {
			$args = array(
				'post_type'         => 'academy_announcement',
				'post_status'       => 'publish',
				'post__in'          => $announcement_ids,
				'posts_per_page'    => count( $announcement_ids ),
			);
			$announcements = get_posts( $args );
			return $announcements;
		}
		return false;
	}

	public static function get_course_qas( $course_id ) {
		$args = array(
			'status' => 'any',
			'post_id' => $course_id,
			'type' => 'academy_qa',
		);

		$comments = get_comments( $args );
		foreach ( $comments as $comment ) {
			$meta = get_comment_meta( $comment->comment_ID );
			$comment->title = ! empty( $meta['academy_question_title'] ) ? current( $meta['academy_question_title'] ) : '';
		}

		return $comments;
	}

	public static function get_course_lesson_comments( $lesson_id ) {
		if ( ! $lesson_id ) {
			return [];
		}
		$args = array(
			'status' => 'any',
			'post_id' => $lesson_id,
			'type' => 'comment',
		);

		$comments = get_comments( $args );

		return $comments;
	}

	public static function is_course_curriculum( $course_id, $topic_id, $topic_type ): bool {
		$course_curriculum = get_post_meta( $course_id, 'academy_course_curriculum', true );

		foreach ( (array) $course_curriculum as $curriculum ) {
			foreach ( $curriculum['topics'] as $topic ) {
				if ( 'sub-curriculum' === $topic['type'] ) {
					foreach ( $topic['topics'] as $sub_topic ) {
						if ( (int) $topic_id === (int) $sub_topic['id'] && $topic_type === $sub_topic['type'] ) {
							return true;
						}
					}
				}
				if ( (int) $topic_id === (int) $topic['id'] && $topic_type === $topic['type'] ) {
					return true;
				}
			}
		}

		return false;
	}

	public static function get_search_courses( $search_keyword ) {
		global $wpdb;

		$wild = '%';
		$like = $wild . $wpdb->esc_like( $search_keyword ) . $wild;

		$post_status = [ 'publish', 'private' ];
		$status_placeholders = implode( ',', array_fill( 0, count( $post_status ), '%s' ) );

		$sql = "SELECT ID, post_title
			FROM {$wpdb->posts}
			WHERE (post_title LIKE %s OR CAST(ID AS CHAR) LIKE %s)
			AND post_type = 'academy_courses'
			AND post_status IN ($status_placeholders)
		";

		$params = array_merge( [ $like, $like ], $post_status );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom query with no WP API equivalent
		$results = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $results;
	}
	/**
	 * Delete course review comments and rating meta.
	 *
	 * @param int $course_id Course ID.
	 * @param int $user_id   User ID.
	 */
	public static function delete_comment_and_review( $course_id, $user_id ) {
		global $wpdb;

		$course_id = (int) $course_id;
		$user_id   = (int) $user_id;

		// Get comment IDs first.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom query with no WP API equivalent
		$comment_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT comment_ID 
				FROM {$wpdb->comments}
				WHERE comment_post_ID = %d
				AND user_id = %d
				AND comment_type IN ( %s, %s, %s, %s )",
				$course_id,
				$user_id,
				'academy_courses',
				'course_completed',
				'academy_qa',
				'academy_assignments'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

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

		$comment_ids = array_map( 'intval', $comment_ids );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- bulk delete; clean_comment_cache() below.
		$wpdb->query(
			$wpdb->prepare(
				"DELETE c, cm
				FROM {$wpdb->comments} AS c
				LEFT JOIN {$wpdb->commentmeta} AS cm
				ON cm.comment_id = c.comment_ID
				WHERE c.comment_ID IN (" . implode( ',', array_fill( 0, count( $comment_ids ), '%d' ) ) . ')',
				$comment_ids
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		clean_comment_cache( $comment_ids );
	}
}