<?php

/**
 * Analytics controller.
 *
 * @since 1.6.7
 */

namespace Masteriyo\RestApi\Controllers\Version1;

defined( 'ABSPATH' ) || exit;

use Masteriyo\Enums\OrderStatus;
use Masteriyo\Enums\PostStatus;
use Masteriyo\Helper\Permission;
use Masteriyo\PostType\PostType;
use Masteriyo\DateTime;
use Masteriyo\Enums\CommentStatus;
use Masteriyo\Enums\CommentType;
use Masteriyo\Enums\UserCourseStatus;
use Masteriyo\Roles;
use WP_Error;

class AnalyticsController extends CrudController {


	/**
	 * Route base.
	 *
	 * @since 1.6.7
	 *
	 * @var string
	 */
	protected $rest_base = 'analytics';

	/**
	 * Permission class.
	 *
	 * @since 1.6.7
	 *
	 * @var Permission
	 */
	protected $permission;

	/**
	 * Object type.
	 *
	 * @since 1.6.7
	 *
	 * @var string
	 */
	protected $object_type = 'analytics';

	/**
	 * Constructor.
	 *
	 * @since 1.6.7
	 *
	 * @param Permission $permission
	 */
	public function __construct( Permission $permission ) {
		$this->permission = $permission;

		add_action( 'masteriyo_new_order', array( $this, 'delete_sales_related_cache_keys' ), 10, 2 );
		add_action( 'masteriyo_update_order', array( $this, 'delete_sales_related_cache_keys' ), 10, 2 );
		add_action( 'masteriyo_trash_order', array( $this, 'delete_sales_related_cache_keys' ), 10, 2 );

		add_action( 'masteriyo_new_user_course', array( $this, 'delete_user_courses_related_cache_keys' ), 10, 2 );
		add_action( 'masteriyo_update_user_course', array( $this, 'delete_user_courses_related_cache_keys' ), 10, 2 );
		add_action( 'masteriyo_delete_user_course', array( $this, 'delete_user_courses_related_cache_keys' ), 10, 2 );
	}

	/**
	 * Deletes sales-related cache keys.
	 *
	 * This function deletes the sales-related cache keys by clearing the caches
	 * for the 'analytics_sales_group' transient. It takes two parameters:
	 * - $id: The ID of the sales-related cache key.
	 * - $order: An instance of the \Masteriyo\Models\Order\Order class.
	 *
	 * @since 2.14.0
	 *
	 * @param mixed $id The ID of the sales-related cache key.
	 * @param \Masteriyo\Models\Order\Order $order An instance of the \Masteriyo\Models\Order\Order class.
	 *
	 * @return void
	 */
	public function delete_sales_related_cache_keys( $id, $order ) {
		if ( ! $id || ! ( $order instanceof \Masteriyo\Models\Order\Order ) ) {
			return;
		}

		$items = $order->get_items( 'course' );

		if ( ! empty( $items ) ) {
			masteriyo_transient_cache()->clear_caches( 'analytics_sales_group' );
		}
	}

	/**
	 * Deletes user courses related cache keys.
	 *
	 * This function deletes the user courses related cache keys by clearing the caches
	 * for the 'analytics_user_courses_group' transient. It takes two parameters:
	 * - $id: The ID of the user courses related cache key.
	 * - $user_course: An instance of the \Masteriyo\Models\UserCourse class.
	 *
	 * @since 2.14.0
	 *
	 * @param mixed $id The ID of the user courses related cache key.
	 * @param \Masteriyo\Models\UserCourse $user_course An instance of the \Masteriyo\Models\UserCourse class.
	 *
	 * @return void
	 */
	public function delete_user_courses_related_cache_keys( $id, $user_course ) {
		if ( ! $id || ! ( $user_course instanceof \Masteriyo\Models\UserCourse ) ) {
			return;
		}

		masteriyo_transient_cache()->clear_caches( 'analytics_user_courses_group' );
	}

	/**
	 * Generates an array of cache keys for analytics data based on the provided user ID, start date, and end date.
	 *
	 * @since 2.14.0
	 *
	 * @param int|null $user_id The ID of the user (default: null)
	 * @param string|null $start_date The start date for the analytics data (default: null)
	 * @param string|null $end_date The end date for the analytics data (default: null)
	 *
	 * @return array An array of cache keys for analytics data
	 */
	private function analytics_cache_keys( $user_id = null, $start_date = null, $end_date = null ) {
		$is_admin_or_manager  = masteriyo_is_current_user_admin() || masteriyo_is_current_user_manager();
		$current_user_id      = $user_id ? $user_id : get_current_user_id();
		$start_date_formatted = $start_date ? gmdate( 'Y-m-d', strtotime( $start_date ) ) : 'no_start_date';
		$end_date_formatted   = $end_date ? gmdate( 'Y-m-d', strtotime( $end_date ) ) : 'no_end_date';

		return array(
			'sales_data'            => 'analytics_sales_data_' . ( $is_admin_or_manager ? 'all_' : $current_user_id . '_' ) . $start_date_formatted . '_' . $end_date_formatted,
			'enrolled_courses_data' => 'analytics_enrolled_courses_data_' . ( $is_admin_or_manager ? 'all_' : $current_user_id . '_' ) . $start_date_formatted . '_' . $end_date_formatted,
			'total_earnings'        => 'analytics_total_earnings_' . ( $is_admin_or_manager ? 'all' : $current_user_id ),
			'total_refunds'         => 'analytics_total_refunds_' . ( $is_admin_or_manager ? 'all' : $current_user_id ),
			'total_discounts'       => 'analytics_total_discounts_' . ( $is_admin_or_manager ? 'all' : $current_user_id ),
		);
	}

	/**
	 * Register routes.
	 *
	 * @since 1.6.7
	 */
	public function register_routes() {
		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base,
			array(
				array(
					'methods'             => \WP_REST_Server::READABLE,
					'callback'            => array( $this, 'get_items' ),
					'permission_callback' => array( $this, 'get_items_permissions_check' ),
					'args'                => array(
						'start_date' => array(
							'description'       => __( 'Limit response to resources published after a given ISO8601 compliant date.', 'learning-management-system' ),
							'type'              => 'string',
							'format'            => 'date-time',
							'validate_callback' => 'rest_validate_request_arg',
						),
						'end_date'   => array(
							'description'       => __( 'Limit response to resources published after a given ISO8601 compliant date.', 'learning-management-system' ),
							'type'              => 'string',
							'format'            => 'date-time',
							'validate_callback' => 'rest_validate_request_arg',
						),
					),
				),
			)
		);

		/**
		 *
		 * Register courses analytics route.
		 *
		 * @since 2.14.4
		 */
		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/courses_analytics',
			array(
				array(
					'methods'             => \WP_REST_Server::READABLE,
					'callback'            => array( $this, 'get_courses_analytics' ),
					'permission_callback' => array( $this, 'get_courses_analytics_permissions_check' ),
					'args'                => parent::get_collection_params(),
				),
			)
		);
	}

	/**
	 * Prepare courses analytics data.
	 *
	 * @since 2.14.4
	 *
	 * @param object $request.
	 *
	 * @return array
	 */
	public function get_courses_analytics( $request ) {
		$query_args = parent::prepare_objects_query( $request );

		$query_args['post_type']           = PostType::COURSE;
		$query_args['post_status']         = PostStatus::PUBLISH;
		$query_args['orderby']             = 'date';
		$query_args['post_parent__in']     = array();
		$query_args['post_parent__not_in'] = array();

		$courses_data = $this->get_courses_data();

		$total_posts = $courses_data['total'];
		$course_ids  = $courses_data['ids'];

		$posts_per_page = (int) $query_args['posts_per_page'];
		$current_page   = (int) $query_args['paged'];
		$pages          = (int) ceil( $total_posts / $posts_per_page );

		$offset = ( $current_page - 1 ) * $posts_per_page;

		$limited_course_ids = array_slice( $course_ids, $offset, $posts_per_page );

		$data = array();

		foreach ( $limited_course_ids as $course_id ) {
			$course_data = $this->get_courses_analytics_data( $course_id, $request );
			if ( $course_data !== null ) {
					$data[] = $course_data;
			}
		}

		return array(
			'data' => $data,
			'meta' => array(
				'total'        => $total_posts,
				'pages'        => $pages,
				'current_page' => $current_page,
				'per_page'     => $posts_per_page,
			),
		);
	}

	/**
	 * Prepare course analytics data according to course id.
	 *
	 * @since 2.14.4
	 *
	 * @param int $course_id.
	 * @param WP_REST_Request $request.
	 *
	 * @return array
	 */
	public function get_courses_analytics_data( $course_id, $request ) {

		$course = masteriyo_get_course( $course_id );
		$search = isset( $request['search'] ) ? esc_attr( $request['search'] ) : '';

		if ( is_null( $course ) || is_wp_error( $course ) ) {
			return new WP_Error( 'invalid_course', __( 'Invalid course data.' ) );
		}

		$course_name = $course->get_title();

		if ( ! empty( $search ) && stripos( $course_name, $search ) === false ) {
			return null;
		}

		$context = 'view';

		$sections_count = $this->get_sections_count( $course->get_id() );
		$quizzes_count  = $this->get_quizzes_data( $course->get_id() );
		$lessons_count  = $this->get_lessons_data( $course->get_id() );
		$earnings       = $this->get_total_earnings_data( $course->get_id() );
		$quizzes_count  = $quizzes_count['total'];
		$lessons_count  = $lessons_count['total'];
		$author         = masteriyo_get_user( $course->get_author_id( $context ) );

		$featured       = $course->get_featured( $context );
		$students_count = masteriyo_count_enrolled_users( $course->get_id() );

		if ( is_wp_error( $author ) || is_null( $author ) ) {
			$author = null;
		} else {
			$author = array(
				'id'           => $author->get_id(),
				'display_name' => $author->get_display_name(),
				'avatar_url'   => $author->profile_image_url(),
			);
		}

		$data = array(
			'id'             => $course->get_id(),
			'name'           => $course->get_title(),
			'sections_count' => $sections_count,
			'lessons_count'  => $lessons_count,
			'quizzes_count'  => $quizzes_count,
			'earnings'       => $earnings,
			'author'         => $author,
			'featured'       => $featured,
			'students_count' => $students_count,
		);

		/**
		 * Filter course rest response data.
		 *
		 * @since 2.14.4
		 *
		 * @param array $data Course data.
		 * @param Masteriyo\Models\Course $course Course object.
		 * @param string $context What the value is for. Valid values are view and edit.
		 * @param Masteriyo\RestApi\Controllers\Version1\AnalyticsController $controller REST courses controller object.
		 */
		return apply_filters( 'masteriyo_courses_analytics_data', $data, $course, $context, $this );
	}

	/**
	 * Get a collection of courses data.
	 *
	 * @since 1.6.7
	 *
	 * @param \WP_REST_Request $request Full details about the request.
	 *
	 * @return \WP_Error|\WP_REST_Response
	 */
	public function get_items( $request ) {
		$items    = $this->prepare_items_for_response( $request );
		$response = rest_ensure_response( $items );

		/**
		 * Filter the data for a response.
		 *
		 * The dynamic portion of the hook name, $this->object_type,
		 * refers to object type being prepared for the response.
		 *
		 * @since 1.6.7
		 *
		 * @param \WP_REST_Response $response The response object.
		 * @param array             $items Analytics data.
		 * @param \WP_REST_Request  $request  Request object.
		 */
		return apply_filters( "masteriyo_rest_prepare_{$this->object_type}_object", $response, $items, $request );
	}

	/**
	 * Check if a given request has access to read items.
	 *
	 * @since 1.6.7
	 *
	 * @param  \WP_REST_Request $request Full details about the request.
	 * @return \WP_Error|boolean
	 */
	public function get_items_permissions_check( $request ) {
		if ( is_null( $this->permission ) ) {
			return new \WP_Error(
				'masteriyo_null_permission',
				__( 'Sorry, the permission object for this resource is null.', 'learning-management-system' )
			);
		}

		return current_user_can( 'manage_options' ) || current_user_can( 'manage_masteriyo_settings' ) || current_user_can( 'edit_courses' );
	}

	/**
	 * Check if a given request has access to read items for courses analytics.
	 *
	 * @since 2.14.4
	 *
	 * @param  \WP_REST_Request $request Full details about the request.
	 * @return \WP_Error|boolean
	 */
	public function get_courses_analytics_permissions_check( $request ) {
		if ( is_null( $this->permission ) ) {
			return new \WP_Error(
				'masteriyo_null_permission',
				__( 'Sorry, the permission object for this resource is null.', 'learning-management-system' )
			);
		}

		if ( masteriyo_is_current_user_admin() || masteriyo_is_current_user_manager() ) {
			return true;
		}

		return current_user_can( 'manage_options' ) || current_user_can( 'manage_masteriyo_settings' ) || current_user_can( 'edit_courses' );
	}

	/**
	 * Prepare items for response.
	 *
	 * @since 1.6.7
	 *
	 * @param \WP_REST_Request $request Full data about the request.
	 *
	 * @return \WP_REST_Response
	 */
	protected function prepare_items_for_response( \WP_REST_Request $request ) {
		$start_date = $request->get_param( 'start_date' );
		$end_date   = $request->get_param( 'end_date' );
		$items      = array();

		$courses_data = $this->get_courses_data();
		$course_ids   = $courses_data['ids'];

		$items['courses'] = array(
			'total' => $courses_data['total'],
		);

		$items['lessons']           = $this->get_lessons_data( $course_ids );
		$items['quizzes']           = $this->get_quizzes_data( $course_ids );
		$items['questions']         = $this->get_questions_data( $course_ids );
		$items['questions_answers'] = $this->get_questions_answers_data( $course_ids );
		$items['reviews']           = $this->get_reviews_data( $course_ids );
		$items['instructors']       = $this->get_instructors_data();
		$items['user_courses']      = $this->get_enrolled_courses_data( $course_ids, $start_date, $end_date );
		$items['sales']             = $this->get_sales_data( $course_ids, $start_date, $end_date );
		$items['popular_courses']   = $this->get_most_popular_courses_data();
		$items['recent_reviews']    = $this->get_recent_reviews_data();
		$items['new_students']      = $this->get_newly_registered_students();
		$items['new_instructors']   = $this->get_newly_registered_instructors();
		$items['is_admin']          = masteriyo_is_current_user_admin();

		/**
		 * Filters rest prepared analytics items.
		 *
		 * @since 1.6.7
		 *
		 * @param array $items Items data.
		 * @param \WP_REST_Request $request Request.
		 */
		return apply_filters( 'masteriyo_rest_prepared_analytics_items', $items, $request );
	}

	/**
	 * Get courses data.
	 *
	 * @since 1.6.7
	 *
	 * @return array
	 */
	protected function get_courses_data() {
		$query = new \WP_Query(
			array(
				'post_status'    => PostStatus::PUBLISH,
				'post_type'      => PostType::COURSE,
				'posts_per_page' => -1,
				'author'         => masteriyo_is_current_user_admin() || masteriyo_is_current_user_manager() ? null : get_current_user_id(),
				'fields'         => 'ids',
			)
		);

		return array(
			'ids'   => $query->posts,
			'total' => $query->post_count,
		);
	}

	/**
	 * Get lessons count.
	 *
	 * @since 1.6.7
	 *
	 * @param array $course_ids Course IDs.
	 *
	 * @return array
	 */
	protected function get_lessons_data( $course_ids ) {
		$data = array(
			'total' => 0,
		);

		if ( $course_ids ) {
			$query         = new \WP_Query(
				array(
					'post_status'    => PostStatus::PUBLISH,
					'post_type'      => PostType::LESSON,
					'posts_per_page' => 1,
					'meta_query'     => array(
						array(
							'key'     => '_course_id',
							'value'   => $course_ids,
							'compare' => 'IN',
						),
					),
					'fields'         => 'ids',
				)
			);
			$data['total'] = $query->found_posts;
		}

		return $data;
	}

	/**
	 * Get quizzes count.
	 *
	 * @since 1.6.7
	 *
	 * @param array $course_ids Course IDs.
	 *
	 * @return array
	 */
	protected function get_quizzes_data( $course_ids ) {
		$data = array(
			'total' => 0,
		);

		if ( $course_ids ) {
			$query         = new \WP_Query(
				array(
					'post_status'    => PostStatus::PUBLISH,
					'post_type'      => PostType::QUIZ,
					'posts_per_page' => 1,
					'meta_query'     => array(
						array(
							'key'     => '_course_id',
							'value'   => $course_ids,
							'compare' => 'IN',
						),
					),
					'fields'         => 'ids',
				)
			);
			$data['total'] = $query->found_posts;
		}

		return $data;
	}

	/**
	 * Get questions count.
	 *
	 * @since 1.6.7
	 *
	 * @param array $course_ids Course IDs.
	 *
	 * @return array
	 */
	protected function get_questions_data( $course_ids ) {
		$data = array(
			'total' => 0,
		);

		if ( $course_ids ) {
			$query         = new \WP_Query(
				array(
					'post_status'    => PostStatus::PUBLISH,
					'post_type'      => PostType::QUESTION,
					'posts_per_page' => 1,
					'meta_query'     => array(
						array(
							'key'     => '_course_id',
							'value'   => $course_ids,
							'compare' => 'IN',
						),
					),
					'fields'         => 'ids',
				)
			);
			$data['total'] = $query->found_posts;
		}

		return $data;
	}

	/**
	 * Get instructors count.
	 *
	 * @since 1.6.7
	 *
	 * @param array $course_ids Course IDs.
	 *
	 * @return array
	 */
	protected function get_instructors_data() {
		$query = new \WP_User_Query(
			array(
				'role'        => Roles::INSTRUCTOR,
				'number'      => 1,
				'fields'      => 'ids',
				'count_total' => true,
			)
		);

		return array(
			'total' => $query->get_total(),
		);
	}


	/**
	 * Get reviews count.
	 *
	 * @since 1.6.7
	 *
	 * @param array $course_ids Course IDs.
	 *
	 * @return array
	 */
	protected function get_reviews_data( $course_ids ) {
		$data = array(
			'total' => 0,
		);

		if ( $course_ids ) {
			$query         = new \WP_Comment_Query(
				array(
					'type'     => CommentType::COURSE_REVIEW,
					'status'   => CommentStatus::APPROVE_STR,
					'post__in' => $course_ids,
					'count'    => true,
					'number'   => 1,
				)
			);
			$data['total'] = $query->get_comments();
		}

		return $data;
	}

	/**
	 * Get question/answers count.
	 *
	 * @since 1.6.7
	 *
	 * @param array $course_ids Course IDs.
	 *
	 * @return array
	 */
	protected function get_questions_answers_data( $course_ids ) {
		$data = array(
			'total' => 0,
		);

		if ( $course_ids ) {
			$query         = new \WP_Comment_Query(
				array(
					'type'     => CommentType::COURSE_QA,
					'status'   => CommentStatus::APPROVE_STR,
					'count'    => true,
					'post__in' => $course_ids,
					'number'   => 1,
				)
			);
			$data['total'] = $query->get_comments();
		}

		return $data;
	}

	/**
	 * Get enrolled courses data.
	 *
	 * @since 1.6.7
	 *
	 * @param \WP_REST_Request $request Request.
	 * @return array
	 */
	protected function get_enrolled_courses_data( $course_ids, $start_date, $end_date ) {
		$cache     = masteriyo_transient_cache();
		$cache_key = $this->analytics_cache_keys( null, $start_date, $end_date )['enrolled_courses_data'];
		$data      = $cache->get_cache( $cache_key, 'analytics_user_courses_group' );

		if ( ! is_null( $data ) ) {
			return $data;
		}

		global $wpdb;

		$data = array();

		$data['total']    = masteriyo_get_user_courses_count_by_course( $course_ids );
		$data['students'] = masteriyo_count_enrolled_users( $course_ids );

		if ( $course_ids ) {
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
			$data['data'] = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT DATE(date_start) as date, COUNT(*) as count
					FROM {$wpdb->prefix}masteriyo_user_items
					WHERE item_id IN (" . implode( ',', $course_ids ) . ')
					AND status = %s
					AND DATE(date_start) >= %s AND DATE(date_start) <= %s
					GROUP BY DATE(date_start)',
					UserCourseStatus::ACTIVE,
					$start_date,
					$end_date
				),
				ARRAY_A
			);
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
		}

		$data['data'] = $this->format_series_data( array_values( $data['data'] ?? array() ), $start_date, $end_date, '1 day' );

		$cache->set_cache( $cache_key, $data, DAY_IN_SECONDS, 'analytics_user_courses_group' );

		return $data;
	}

	/**
	 * Get sales data.
	 *
	 * @since 1.6.7
	 *
	 * @param array $course_ids Course IDs.
	 * @param string $start_date Start date.
	 * @param string $end_date   End date.
	 *
	 * @return array
	 */
	protected function get_sales_data( $course_ids, $start_date, $end_date ) {
		$data = array(
			'total_earnings'  => 0,
			'total_refunds'   => 0,
			'total_discounts' => 0,
			'earnings'        => array( 'data' => array() ),
			'refunds'         => array( 'data' => array() ),
		);

		$cache      = masteriyo_transient_cache();
		$cache_key  = $this->analytics_cache_keys( null, $start_date, $end_date )['sales_data'];
		$all_orders = $cache->get_cache( $cache_key, 'analytics_sales_group' );

		if ( is_null( $all_orders ) ) {
			$all_orders = $this->get_orders_within_range( $course_ids, $start_date, $end_date );
			$cache->set_cache( $cache_key, $all_orders, DAY_IN_SECONDS, 'analytics_sales_group' );
		}

		if ( ! empty( $all_orders ) ) {
			$all_orders = array_map(
				function( $all_order ) {
					$all_order['amount'] = masteriyo_format_decimal( $all_order['amount'] );

					return $all_order;
				},
				$all_orders
			);

			foreach ( $all_orders as $result ) {
				if ( OrderStatus::COMPLETED === $result['status'] ) {
					$data['earnings']['data'][] = $result;
				} elseif ( OrderStatus::REFUNDED === $result['status'] ) {
					$data['refunds']['data'][] = $result;
				}
			}

			$data['earnings']['data'] = $this->format_series_data( $data['earnings']['data'], $start_date, $end_date, '1 day' );
			$data['refunds']['data']  = $this->format_series_data( $data['refunds']['data'], $start_date, $end_date, '1 day' );
		} else {
			$data['earnings']['data'] = $this->format_series_data( array(), $start_date, $end_date, '1 day' );
			$data['refunds']['data']  = $this->format_series_data( array(), $start_date, $end_date, '1 day' );
		}

		$data['total_earnings']  = $this->get_total_earnings( $course_ids );
		$data['total_refunds']   = $this->get_total_refunds( $course_ids );
		$data['total_discounts'] = $this->get_total_discounts( $course_ids );

		/**
		 * Filters the sales data returned by the `get_sales_data()` method.
		 *
		 * @since 2.12.0
		 *
		 * @param array $data The sales data array.
		 * @param string $start_date The start date of the date range.
		 * @param string $end_date The end date of the date range.
		 * @param array $course_ids The course IDs to filter the sales data by.
		 * @param self $this The current instance of this controller.
		 *
		 * self is added since 2.14.0
		 *
		 * @return array The filtered sales data.
		 */
		return apply_filters( 'masteriyo_sales_data', $data, $start_date, $end_date, $course_ids, $this );
	}

	/**
	 * Retrieves orders within the specified date range.
	 *
	 * @since 2.11.0
	 *
	 * @param array $course_ids Course IDs.
	 * @param string $start_date The start date of the date range.
	 * @param string $end_date The end date of the date range.
	 *
	 * @return array An array of orders within the specified date range.
	 */
	private function get_orders_within_range( $course_ids, $start_date, $end_date ) {
		if ( empty( $course_ids ) ) {
			return array();
		}

		global $wpdb;

		$course_ids_placeholder = implode( ',', array_fill( 0, count( $course_ids ), '%d' ) );

		$query_params = array_merge(
			$course_ids,
			array(
				PostType::ORDER,
				OrderStatus::COMPLETED,
				OrderStatus::REFUNDED,
				$start_date,
				$end_date,
			)
		);

    // phpcs:disable
    $orders_query = $wpdb->prepare(
        "
        SELECT
            DATE(p.post_modified) AS date,
            p.post_status AS status,
            COUNT(*) AS count,
            SUM(
                CASE
                    WHEN meta_conversion.meta_value IS NOT NULL AND meta_conversion.meta_value != ''
										THEN CAST(meta_conversion.meta_value AS DECIMAL(10, 2))
										ELSE CAST(pm.meta_value AS DECIMAL(10, 2))
                END
            ) AS amount
        FROM {$wpdb->posts} AS p
        LEFT JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id AND pm.meta_key = '_total'
        LEFT JOIN {$wpdb->postmeta} AS meta_conversion ON p.ID = meta_conversion.post_id AND meta_conversion.meta_key = '_conversion_total'
        INNER JOIN {$wpdb->prefix}masteriyo_order_items oi ON p.ID = oi.order_id
        INNER JOIN {$wpdb->prefix}masteriyo_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
        WHERE oim.meta_key = 'course_id'
            AND oim.meta_value IN ($course_ids_placeholder)
            AND p.post_type = %s
            AND p.post_status IN (%s, %s)
            AND p.post_modified >= %s
            AND p.post_modified <= %s
        GROUP BY DATE(p.post_modified), p.post_status
        ORDER BY DATE(p.post_modified) ASC, p.post_status ASC
        ",
				...$query_params
		);

		$orders_results = $wpdb->get_results($orders_query, ARRAY_A);
		// phpcs:enable

		if ( ! $orders_results ) {
			return array();
		}

		$orders_results = array_map(
			function( $orders_result ) {
				$orders_result['amount'] = masteriyo_format_decimal( $orders_result['amount'] );

				return $orders_result;
			},
			$orders_results
		);

		return $orders_results;
	}

	/**
	 * Retrieves the total amount based on the provided course IDs, status, and meta keys.
	 *
	 * @since 2.14.0
	 *
	 * @param array $course_ids The IDs of the courses.
	 * @param string $status The post status (e.g., 'completed', 'refunded').
	 * @param string $meta_key1 The primary meta key to check (e.g., '_conversion_total').
	 * @param string $meta_key2 The fallback meta key (e.g., '_total').
	 * @param bool $is_needs_admin_or_manager Whether to check whether the user is an admin or manager.
	 * @param string $analytics_cache_key The cache key for analytics.
	 * @param string $cache_key_group The cache group for analytics.
	 *
	 * @return string The total amount.
	 */
	public function get_total_amount( $course_ids, $status, $meta_key1, $meta_key2, $cache_key = '', $cache_key_group = '', $order_item_type = 'course', $order_item_meta_key = 'course_id', $is_needs_admin_or_manager = true ) {
		$cache     = masteriyo_transient_cache();
		$cache_key = $this->analytics_cache_keys()[ $cache_key ];
		$total     = $cache->get_cache( $cache_key, $cache_key_group );

		if ( ! is_null( $total ) ) {
			return $total;
		}

		global $wpdb;

		$total = 0;

		$is_admin_or_manager = masteriyo_is_current_user_admin() || masteriyo_is_current_user_manager();

		if ( $is_admin_or_manager && $is_needs_admin_or_manager ) {
			// Query for admins/managers
			$query = "
					SELECT SUM(
							CASE
									WHEN conversion_meta.meta_value IS NOT NULL AND conversion_meta.meta_value != ''
									THEN CAST(conversion_meta.meta_value AS DECIMAL(10, 2))
									ELSE CAST(pm.meta_value AS DECIMAL(10, 2))
							END
					) AS total_amount
					FROM {$wpdb->postmeta} pm
					LEFT JOIN {$wpdb->postmeta} conversion_meta
							ON pm.post_id = conversion_meta.post_id AND conversion_meta.meta_key = %s
					JOIN {$wpdb->posts} p ON pm.post_id = p.ID
					WHERE pm.meta_key = %s
					AND p.post_status = %s
					AND p.post_type = %s
			";

			$prepared_query = $wpdb->prepare( $query, $meta_key1, $meta_key2, $status, PostType::ORDER ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		} else {

			if ( empty( $course_ids ) ) {
				return '0';
			}

			// Query for non-admins
			$placeholders = implode( ',', array_fill( 0, count( $course_ids ), '%d' ) );

			// meta_key1 exists and is not empty we have get from this otherwise  meta_key2
			$query = "
					SELECT
						SUM(
								CASE
										WHEN meta1.meta_value IS NOT NULL AND meta1.meta_value != ''
										THEN CAST(meta1.meta_value AS DECIMAL(10, 2))
										ELSE CAST(meta2.meta_value AS DECIMAL(10, 2))
								END
						) AS total
						FROM
							{$wpdb->prefix}masteriyo_order_items AS items
						INNER JOIN
							{$wpdb->prefix}masteriyo_order_itemmeta AS itemmeta ON items.order_item_id = itemmeta.order_item_id
						LEFT JOIN
							{$wpdb->postmeta} AS meta1 ON items.order_id = meta1.post_id AND meta1.meta_key = %s
						LEFT JOIN
							{$wpdb->postmeta} AS meta2 ON items.order_id = meta2.post_id AND meta2.meta_key = %s
						INNER JOIN
							{$wpdb->posts} AS posts ON items.order_id = posts.ID
						WHERE
							items.order_item_type = %s
							AND itemmeta.meta_key = %s
							AND itemmeta.meta_value IN ($placeholders)
							AND posts.post_type = %s
							AND posts.post_status = %s
					";

			// phpcs:disable
			$prepared_query = $wpdb->prepare(
				$query,
				array_merge(
					array( $meta_key1, $meta_key2, $order_item_type, $order_item_meta_key ),
					$course_ids,
					array(  PostType::ORDER, $status )
				)
			);
			// phpcs:enable
		}

		$total = $wpdb->get_var( $prepared_query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		$cache->set_cache( $cache_key, $total, 0, $cache_key_group );
		return is_null( $total ) ? '0' : masteriyo_format_decimal( floatval( $total ) );
	}

	/**
	 * Retrieves the total earnings for the specified course IDs.
	 *
	 * @since 2.12.0
	 *
	 * @param array $course_ids The IDs of the courses to retrieve the total earnings for.
	 *
	 * @return string The total earnings for the specified courses.
	 */
	public function get_total_earnings( $course_ids ) {
		return $this->get_total_amount( $course_ids, OrderStatus::COMPLETED, '_conversion_total', '_total', 'total_earnings', 'analytics_sales_group' );
	}

	/**
	 * Retrieves the total number of refunds for the given course IDs.
	 *
	 * @since 2.12.0
	 *
	 * @param int[] $course_ids The IDs of the courses to get the total refunds for.
	 *
	 * @return string The total number of refunds for the given course IDs.
	 */
	public function get_total_refunds( $course_ids ) {
		return $this->get_total_amount( $course_ids, OrderStatus::REFUNDED, '_conversion_total', '_total', 'total_refunds', 'analytics_sales_group' );
	}

	/**
	 * Retrieves the total discounts for the given course IDs.
	 *
	 * @since 2.12.0
	 *
	 * @param int[] $course_ids The IDs of the courses to get the total discounts for.
	 *
	 * @return string The total discounts for the given course IDs.
	 */
	public function get_total_discounts( $course_ids ) {
		return $this->get_total_amount( $course_ids, OrderStatus::COMPLETED, '_conversion_discount_total', '_discount_total', 'total_discounts', 'analytics_sales_group' );
	}

	/**
	 * Retrieves the total earnings for the specified course IDs.
	 *
	 * @since 2.14.4
	 *
	 * @param array $course_ids The IDs of the courses to retrieve the total earnings for.
	 *
	 * @return string The total earnings for the specified courses.
	 */
	private function get_total_earnings_data( $course_id ) {
		global $wpdb;

		// Prepare the query parameters
		$query_params = array(
			$course_id,
			PostType::ORDER,
			OrderStatus::COMPLETED,
		);

		// phpcs:disable
		$orders_query = $wpdb->prepare(
			"
			SELECT
				SUM(
					CASE
						WHEN meta_conversion.meta_value IS NOT NULL AND meta_conversion.meta_value != ''
						THEN meta_conversion.meta_value
						ELSE pm.meta_value
					END
				) AS total_amount
			FROM {$wpdb->posts} AS p
			LEFT JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id AND pm.meta_key = '_total'
			LEFT JOIN {$wpdb->postmeta} AS meta_conversion ON p.ID = meta_conversion.post_id AND meta_conversion.meta_key = '_conversion_total'
			INNER JOIN {$wpdb->prefix}masteriyo_order_items oi ON p.ID = oi.order_id
			INNER JOIN {$wpdb->prefix}masteriyo_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
			WHERE oim.meta_key = 'course_id'
				AND oim.meta_value = %d
				AND p.post_type = %s
				AND p.post_status = %s
			",
			...$query_params
		);
	
		$total_amount = $wpdb->get_var($orders_query);

		// Return the total amount as an integer (0 if no results found)
		$total_amount = masteriyo_format_decimal( $total_amount );
		$total_amount = $total_amount ? $total_amount : 0;
		return $total_amount;
	}


	/**
	 * Get sections count.
	 *
	 * @since 2.14.4
	 *
	 * @param int $course_id Course ID.
	 *
	 * @return int
	 */
	protected function get_sections_count( $course_ids ) {
		$data = array(
			'total' => 0,
		);

		if ( $course_ids ) {
			$query = new \WP_Query(
				array(
					'post_status'    => PostStatus::PUBLISH,
					'post_type'      => PostType::SECTION,
					'posts_per_page' => 1,
					'meta_query'     => array(
						array(
							'key'     => '_course_id',
							'value'   => $course_ids,
							'compare' => 'IN',
						),
					),
					'fields'         => 'ids',
				)
			);
			$data  = $query->found_posts;
		}

		return $data;
	}

	/**
	 * Format series data.
	 *
	 * Prefills empty data with 0.
	 *
	 * @since 1.6.7
	 *
	 * @param array $data Table name.
	 * @param DateTime $start Start date.
	 * @param DateTime $end End date.
	 * @param string $interval Interval.
	 */
	protected function format_series_data( $data, $start, $end, $interval ) {
		$start = new \DateTime( $start );
		$end   = new \DateTime( $end );

		$end->modify( '+1 day' );

		$interval       = \DateInterval::createFromDateString( $interval );
		$period         = new \DatePeriod( $start, $interval, $end );
		$formatted_data = array();

		foreach ( $period as $date ) {
			$date    = $date->format( 'Y-m-d' );
			$found   = array_search( $date, array_column( $data, 'date' ), true );
			$current = array();

			if ( false !== $found ) {
				$current = isset( $data[ $found ] ) ? $data[ $found ] : array();
			}

			$formatted_data[] = array(
				'date'   => $date,
				'count'  => $current['count'] ?? 0,
				'status' => $current['status'] ?? null,
				'amount' => $current['amount'] ?? null,
			);
		}

		return $formatted_data;
	}

	/**
	 * Fetch the most popular courses based on the number of enrolled users and review counts.
	 *
	 * @since 2.6.11
	 *
	 * @param int $count The number of popular courses to fetch. Default is 5.
	 *
	 * @return array An array of most popular courses.
	 */
	protected function get_most_popular_courses_data( $count = 5 ) {
		$current_user_id = get_current_user_id();

		$cache_key   = 'masteriyo_popular_courses_' . $current_user_id;
		$cached_data = get_transient( $cache_key );

		if ( $cached_data ) {
			return $cached_data;
		}

		global $wpdb;

		if ( ! $wpdb ) {
			return array();
		}

		$role_conditions = '';

		if ( ! masteriyo_is_current_user_admin() ) {
			$role_conditions = "AND p.post_author = {$current_user_id}";
		}

		// Get enrolled users count.
		$sql_enrolled_users = "
			SELECT ui.item_id, COUNT(*) as enrolled_count
			FROM {$wpdb->prefix}masteriyo_user_items ui
			JOIN {$wpdb->prefix}posts p ON ui.item_id = p.ID
			WHERE ui.status = 'active' AND ui.item_type = 'user_course' AND p.post_status = 'publish' {$role_conditions}
			GROUP BY ui.item_id
		";

		// Get course review count.
		$sql_course_reviews = "
			SELECT p.ID as item_id, pm.meta_value as review_count
			FROM {$wpdb->prefix}posts p
			JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
			WHERE p.post_status = 'publish' AND pm.meta_key = '_review_count'
		";

		// Get popular courses.
		$sql_popular_courses = "
			SELECT e.item_id, e.enrolled_count, IFNULL(r.review_count, 0) as review_count
			FROM ({$sql_enrolled_users}) e
			LEFT JOIN ({$sql_course_reviews}) r ON e.item_id = r.item_id
			ORDER BY e.enrolled_count DESC, IFNULL(r.review_count, 0) DESC
			LIMIT %d
		";

		$popular_courses = $wpdb->get_results( $wpdb->prepare( $sql_popular_courses, $count ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		$courses = array_filter(
			array_map(
				function ( $item ) {
					$course = masteriyo_get_course( $item['item_id'] );

					if ( is_null( $course ) || is_wp_error( $course ) ) {
						return null;
					}

					return array(
						'id'             => $course->get_id(),
						'name'           => $course->get_name(),
						'date_created'   => masteriyo_rest_prepare_date_response( $course->get_date_created() ),
						'date_modified'  => masteriyo_rest_prepare_date_response( $course->get_date_modified() ),
						'price'          => $course->get_price(),
						'price_type'     => $course->get_price_type(),
						'access_mode'    => $course->get_access_mode(),
						'edit_link'      => admin_url( "admin.php?page=masteriyo#/courses/{$course->get_id()}/edit" ),
						'enrolled_count' => $item['enrolled_count'],
						'review_count'   => $item['review_count'],
					);
				},
				$popular_courses
			)
		);

		// Save data to cache.
		set_transient( $cache_key, $courses, HOUR_IN_SECONDS );

		return $courses;
	}

	/**
	 * Fetch the most recent course reviews.
	 *
	 * This function queries the WordPress database to retrieve the most recent
	 * reviews for courses, along with associated course and user information.
	 *
	 * @since 2.6.11
	 *
	 * @param int $count The number of recent reviews to fetch. Default is 5.
	 *
	 * @return array An array of associative arrays, each containing details of a review.
	 */
	protected function get_recent_reviews_data( $count = 5 ) {
		$current_user_id = get_current_user_id();

		$cache_key   = 'masteriyo_recent_reviews_' . $current_user_id;
		$cached_data = get_transient( $cache_key );

		if ( $cached_data ) {
			return $cached_data;
		}

		global $wpdb;

		if ( ! $wpdb ) {
			return array();
		}

		$role_conditions = '';

		if ( ! masteriyo_is_current_user_admin() ) {
			$role_conditions = "AND p.post_author = {$current_user_id}";
		}

		$sql_recent_reviews = "
			SELECT c.comment_ID as review_id, c.comment_post_ID as course_id, p.post_title as course_name,
						c.comment_content as review_content, c.comment_karma as review_rating,
						c.comment_date as review_date, cm.meta_value as review_title, c.user_id, u.display_name as user_name
			FROM {$wpdb->prefix}comments c
			JOIN {$wpdb->prefix}posts p ON c.comment_post_ID = p.ID
			JOIN {$wpdb->prefix}users u ON c.user_id = u.ID
			LEFT JOIN {$wpdb->prefix}commentmeta cm ON c.comment_ID = cm.comment_id AND cm.meta_key = '_title'
			WHERE c.comment_type = 'mto_course_review' AND p.post_status = 'publish' AND c.comment_approved = 1
			{$role_conditions}
			ORDER BY c.comment_date DESC
			LIMIT %d
			";

		$recent_reviews = $wpdb->get_results( $wpdb->prepare( $sql_recent_reviews, $count ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		if ( empty( $recent_reviews ) ) {
			return array();
		}

		$reviews = array_map(
			function ( $item ) {
				return array(
					'review_id'      => absint( $item['review_id'] ),
					'course_id'      => absint( $item['course_id'] ),
					'course_name'    => $item['course_name'],
					'review_title'   => $item['review_title'],
					'review_content' => $item['review_content'],
					'review_rating'  => absint( $item['review_rating'] ),
					'review_date'    => masteriyo_rest_prepare_date_response( $item['review_date'] ),
					'user_id'        => $item['user_id'],
					'user_name'      => $item['user_name'],
				);
			},
			$recent_reviews
		);

		set_transient( $cache_key, $reviews, HOUR_IN_SECONDS );

		return $reviews;
	}

	/**
	 * Fetch newly registered users based on their role and provided criteria.
	 *
	 * @since 2.6.11
	 *
	 * @param string $role The role of the users to fetch (e.g., Roles::STUDENT or Roles::INSTRUCTOR).
	 * @param int $count The number of users to fetch. Default is 5.
	 *
	 * @return array An array of newly registered users with their id, username, email, full name, and registered date.
	 */
	protected function get_newly_registered_users_by_role( $role, $count = 5 ) {
		if ( ! masteriyo_is_current_user_admin() ) {
			return array();
		}

		$args = array(
			'role'    => $role,
			'number'  => $count,
			'orderby' => 'registered',
			'order'   => 'DESC',
			'fields'  => array( 'ID', 'user_login', 'user_email', 'user_registered' ),
		);

		$user_query = new \WP_User_Query( $args );
		$users      = $user_query->get_results();

		$newly_registered_users = array();

		foreach ( $users as $user ) {
			$first_name = get_user_meta( $user->ID, 'first_name', true );
			$last_name  = get_user_meta( $user->ID, 'last_name', true );
			$full_name  = trim( "{$first_name} {$last_name}" );

			$newly_registered_users[] = array(
				'id'              => $user->ID,
				'username'        => $user->user_login,
				'email'           => $user->user_email,
				'registered_date' => $user->user_registered,
				'full_name'       => empty( $full_name ) ? _x( 'N/A', 'full name not available', 'learning-management-system' ) : $full_name,
			);
		}

		return $newly_registered_users;
	}

	/**
	 * Fetch newly registered students based on the provided criteria.
	 *
	 * @since 2.6.11
	 *
	 * @param int $count The number of students to fetch. Default is 5.
	 *
	 * @return array An array of newly registered students.
	 */
	protected function get_newly_registered_students( $count = 5 ) {
		return $this->get_newly_registered_users_by_role( Roles::STUDENT, $count );
	}

	/**
	 * Fetch newly registered instructors based on the provided criteria.
	 *
	 * @since 2.6.11
	 *
	 * @param int $count The number of instructors to fetch. Default is 5.
	 *
	 * @return array An array of newly registered instructors.
	 */
	protected function get_newly_registered_instructors( $count = 5 ) {
		return $this->get_newly_registered_users_by_role( Roles::INSTRUCTOR, $count );
	}
}
