<?php
/**
 * Analytics for instructor
 *
 * @package TutorPro\Report
 * @author Themeum <support@themeum.com>
 * @link https://themeum.com
 * @since 1.9.8
 */

namespace TUTOR_REPORT;

use TUTOR\Input;

defined( 'ABSPATH' ) || exit;

use Tutor\Models\OrderModel;
use Tutor\Ecommerce\Settings;
use Tutor\Helpers\QueryHelper;
use TUTOR_REPORT\ExportAnalytics;
use Tutor\Ecommerce\OrderController;

/**
 * Class Analytics
 */
class Analytics extends ExportAnalytics {

	/**
	 * Resolve dependencies
	 *
	 * @since 1.9.8
	 * @var mixed
	 */
	public $current_page;

	/**
	 * Register hooks
	 */
	public function __construct() {
		add_filter( 'tutor_dashboard/instructor_nav_items', array( $this, 'tutor_analytics_register_menu' ), 20 );
		add_filter( 'load_dashboard_template_part_from_other_location', array( $this, 'tutor_analytics_frontend' ) );

		/**
		 * Enqueue styles for analytics
		 *
		 * @since 1.9.8
		 */
		add_action( 'wp_enqueue_scripts', array( $this, 'tutor_analytics_scripts' ) );
		add_action( 'wp_ajax_view_progress', array( $this, 'view_progress' ) );
		add_action( 'wp_ajax_export_analytics', array( $this, 'export_analytics' ) );
	}

	/**
	 * Analytics frontend nav items
	 *
	 * @since 1.9.8
	 *
	 * @param mixed $nav_items nav items.
	 */
	public function tutor_analytics_register_menu( $nav_items ) {
		do_action( 'before_register_frontend_report_nav_item' );

		$nav_items['analytics'] = array(
			'title'    => __( 'Analytics', 'tutor-pro' ),
			'icon'     => 'tutor-icon-chart-pie',
			'auth_cap' => tutor()->instructor_role,
		);
		return apply_filters( 'after_register_frontend_report_nav_item', $nav_items );
	}

	/**
	 * Load frontend report template
	 *
	 * @since 1.9.8
	 *
	 * @param string $template template.
	 */
	public function tutor_analytics_frontend( $template ) {
		global $wp_query;
		$query_vars = $wp_query->query_vars;
		if ( isset( $query_vars['tutor_dashboard_page'] ) && 'analytics' === $query_vars['tutor_dashboard_page'] ) {
			// Set current page.
			if ( isset( $query_vars['tutor_dashboard_sub_page'] ) ) {
				$this->current_page = $query_vars['tutor_dashboard_sub_page'];
			} else {
				$this->current_page = 'overview';
			}
			$new_template = TUTOR_REPORT()->path . 'templates/frontend_analytics.php';
			if ( file_exists( $new_template ) ) {
				return apply_filters( 'tutor_frontend_report_template', $new_template );
			}
		}
		return $template;
	}

	/**
	 * Analytics sub pages
	 *
	 * @return array
	 *
	 * @since 1.9.8
	 */
	public function sub_pages(): array {
		$sub_pages = array(
			'overview'   => array(
				'title' => __( 'Overview', 'tutor-pro' ),
				'url'   => esc_url( tutor_utils()->tutor_dashboard_url() . 'analytics' ),
			),
			'courses'    => array(
				'title' => __( 'Courses', 'tutor-pro' ),
				'url'   => esc_url( tutor_utils()->tutor_dashboard_url() . 'analytics/courses' ),
			),
			'earnings'   => array(
				'title' => __( 'Earnings', 'tutor-pro' ),
				'url'   => esc_url( tutor_utils()->tutor_dashboard_url() . 'analytics/earnings' ),
			),
			'statements' => array(
				'title' => __( 'Statements', 'tutor-pro' ),
				'url'   => esc_url( tutor_utils()->tutor_dashboard_url() . 'analytics/statements' ),
			),
			'students'   => array(
				'title' => __( 'Students', 'tutor-pro' ),
				'url'   => esc_url( tutor_utils()->tutor_dashboard_url() . 'analytics/students' ),
			),
			'export'     => array(
				'title' => __( 'Export', 'tutor-pro' ),
				'url'   => esc_url( tutor_utils()->tutor_dashboard_url() . 'analytics/export' ),
			),
		);
		return apply_filters( 'tutor_analytics_sub_pages', $sub_pages );
	}

	/**
	 * Load sub page
	 *
	 * @param string $page string.
	 *
	 * @since 1.9.8
	 */
	public function load_sub_page( string $page ) {
		$file = TUTOR_REPORT()->path . 'templates/' . $page . '.php';
		if ( file_exists( $file ) ) {
			ob_start();
			include_once $file;
			return apply_filters( 'tutor-analytics-sub-page', ob_get_clean() );
		} else {
			esc_html_e( 'Content Not Found!', 'tutor-pro' );
		}
	}

	/**
	 * Get earnings of current user
	 *
	 * @since 1.9.8
	 *
	 * @param string $period ( today | monthly| yearly ) required | string.
	 * @param int    $user_id user id.
	 *
	 * @return mixed
	 */
	public function get_total_earnings_by_period( string $period, int $user_id ) {
		global $wpdb;
		$period  = sanitize_text_field( $period );
		$user_id = sanitize_text_field( $user_id );

		$complete_status = tutor_utils()->get_earnings_completed_statuses();
		$complete_status = QueryHelper::prepare_in_clause( $complete_status );

		$period_filter = '';
		if ( 'today' === $period ) {
			$period_filter = 'AND DATE(created_at) = CURDATE()';
		}
		if ( 'monthly' === $period ) {
			$period_filter = 'AND MONTH(created_at) = MONTH(CURDATE()) ';
		}
		if ( 'yearly' === $period ) {
			$period_filter = 'AND YEAR(created_at) = YEAR(CURDATE()) ';
		}

		$earnings = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT instructor_amount
				FROM {$wpdb->prefix}tutor_earnings
				WHERE user_id = %d
					AND order_status IN({$complete_status})
					{$period_filter}
				ORDER BY created_at ASC;
			",
				$user_id
			)
		);

		return $earnings;
	}

	/**
	 * Get total enrolled number from all courses
	 *
	 * this method depends on get_courses_by_period method to
	 *
	 * get all courses of a user
	 *
	 * @return total count enrolled users | int
	 *
	 * @since 1.9.8
	 */
	public static function get_courses_with_search_by_user( int $user_id, string $search = '', $post_status = array( 'publish' ) ): int {
		global $wpdb;
		$user_id = sanitize_text_field( $user_id );
		$search  = sanitize_text_field( $search );

		$course_post_type = tutor()->course_post_type;
		$search_term      = '%' . $wpdb->esc_like( $search ) . '%';
		$post_status      = QueryHelper::prepare_in_clause( $post_status );

		$total_item = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT count(ID)
				FROM {$wpdb->posts}
				WHERE post_type = %s
				AND post_status IN ({$post_status})
				AND post_author = %d
				AND (post_title LIKE %s)
			",
				$course_post_type,
				$user_id,
				$search_term
			)
		);

		return $total_item;
	}

	/**
	 * Get all courses of user, period wise ( today | monthly | yearly )
	 *
	 * @param $period | string required, @param $user_id | int required
	 *
	 * @return array of courses
	 *
	 * @since 1.9.8
	 */
	public static function get_courses_with_total_enroll_earning( int $user_id, string $order = '', string $order_by = '', int $offset = 0, int $limit = 10, string $search = '', $post_status = array( 'publish' ) ) {
		global $wpdb;
		$post_type = tutor()->course_post_type;
		$user_id   = sanitize_text_field( $user_id );
		$order     = sanitize_text_field( $order );
		$order_by  = sanitize_text_field( $order_by );
		$offset    = sanitize_text_field( $offset );
		$limit     = sanitize_text_field( $limit );
		$search    = sanitize_text_field( $search );
		// if there is search then make offset 0 to get result.
		if ( '' !== $search ) {
			$offset = 0;
		}
		$search_term     = '%' . $wpdb->esc_like( $search ) . '%';
		$complete_status = tutor_utils()->get_earnings_completed_statuses();
		$complete_status = "'" . implode( "','", $complete_status ) . "'";
		$post_status     = QueryHelper::prepare_in_clause( $post_status );

		$courses = $wpdb->get_results(
			$wpdb->prepare(
				" SELECT post.ID , post.post_title, post.guid, count(enrollment.ID) AS learner
					FROM {$wpdb->posts} AS post
						LEFT JOIN {$wpdb->posts} AS enrollment ON enrollment.post_parent = post.ID
							AND enrollment.post_type = %s
							AND enrollment.post_status = %s
					WHERE post.post_type = %s
						AND post.post_author = %d
						AND post.post_status IN ({$post_status})
						AND ( post.post_title LIKE %s)
					GROUP BY post.ID
					ORDER BY {$order_by} {$order}
					LIMIT %d, %d
				",
				'tutor_enrolled',
				'completed',
				$post_type,
				$user_id,
				$search_term,
				$offset,
				$limit
			)
		);
		return $courses;
	}

	/**
	 * Get total earning from a course for admin
	 *
	 * @param int $course_id | required.
	 * @return total sales
	 */
	public static function get_earnings_by_course( int $course_id ) {
		global $wpdb;

		$total_sales = 0;

		$product_id = get_post_meta( $course_id, '_tutor_course_product_id', true );
		if ( $product_id ) {
			$total_sales = $wpdb->get_var(
				"SELECT SUM( order_item_meta__line_total.meta_value) as order_item_amount
            FROM {$wpdb->posts} AS posts
            INNER JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON posts.ID = order_items.order_id
            INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta__line_total ON (order_items.order_item_id = order_item_meta__line_total.order_item_id)
                AND (order_item_meta__line_total.meta_key = '_line_total')
            INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS order_item_meta__product_id_array ON order_items.order_item_id = order_item_meta__product_id_array.order_item_id
            WHERE posts.post_type IN ( 'shop_order' )
            AND posts.post_status IN ( 'wc-completed' ) AND ( ( order_item_meta__product_id_array.meta_key IN ('_product_id','_variation_id')
            AND order_item_meta__product_id_array.meta_value IN ('{$product_id}') ) );"
			);
		}
		if ( function_exists( 'wc_price' ) ) {
			$total_sales = wc_price( $total_sales );
		}
		return $total_sales;
	}

	/**
	 * Get earning by user_id, optionally can set period ( today | monthly| yearly )
	 *
	 * Optionally can set start date & end date to get earnings from date range
	 *
	 * If period or date range not pass then it will return all time earnings
	 *
	 * Optionally can set course_id for getting specific course data
	 *
	 * @since 1.9.9
	 *
	 * @param $user_id    user id.
	 * @param $period     sorting time period.
	 * @param $start_date start date.
	 * @param $end_date   end date.
	 * @param $course_id  course id.
	 *
	 * @return array
	 */
	public static function get_earnings_by_user( int $user_id, string $period = '', string $start_date = '', string $end_date = '', int $course_id = null ): array {
		global $wpdb;
		$user_id    = sanitize_text_field( $user_id );
		$period     = sanitize_text_field( $period );
		$start_date = sanitize_text_field( $start_date );
		$end_date   = sanitize_text_field( $end_date );

		$period_query = '';
		$group_query  = ' GROUP BY DATE(date_format) ';
		$course_query = '';
		// set additional query for period or date range if condition not meet then get all time data.
		if ( '' !== $period ) {
			$period_query = QueryHelper::get_period_clause( 'earnings.created_at', $period );
		}

		if ( 'today' !== $period ) {
			$group_query = ' GROUP BY MONTH(date_format) ';
		}

		if ( $course_id ) {
			$course_query = " AND course_id = $course_id ";
		}

		if ( '' !== $start_date && '' !== $end_date ) {
			$period_query = " AND  DATE(earnings.created_at) BETWEEN CAST('$start_date' AS DATE) AND CAST('$end_date' AS DATE) ";
			$group_query  = ' GROUP BY DATE(date_format) ';
		}

		/**
		 * Author query added to use same query for admin as well
		 * pass user_id value 0 to get all data for admin
		 *
		 * @since v2.0.0
		 */
		$author_query = '';
		if ( $user_id ) {
			$author_query = "AND earnings.user_id = {$user_id}";
		}
		// Get statuses.
		$complete_status = tutor_utils()->get_earnings_completed_statuses();
		$complete_status = "'" . implode( "','", $complete_status ) . "'";

		$amount_type = is_admin() ? 'earnings.admin_amount' : 'earnings.instructor_amount';
		$amount_rate = is_admin() ? 'earnings.admin_rate' : 'earnings.instructor_rate';

		$amount_condition = "CASE
			WHEN orders.tax_type = 'inclusive' AND earnings.course_price_grand_total > 0
				THEN ( earnings.course_price_grand_total - orders.tax_amount ) * ( $amount_rate/100 )
			ELSE $amount_type
			END";

		$earnings = $wpdb->get_results(
			"SELECT  SUM($amount_condition) AS total,
				DATE(earnings.created_at) AS date_format
			FROM	{$wpdb->prefix}tutor_earnings as earnings 
			LEFT JOIN {$wpdb->prefix}tutor_orders as orders
				ON earnings.order_id = orders.id
			WHERE earnings.order_status IN({$complete_status})
					{$author_query}
					{$course_query}
					{$period_query}
					{$group_query}
			ORDER BY earnings.created_at ASC;
			"
		);

		$total_earnings = 0;
		foreach ( $earnings as $earning ) {
			$total_earnings += $earning->total;
		}

		return array(
			'earnings'       => $earnings,
			'total_earnings' => $total_earnings,
		);
	}

	/**
	 * Get total enrollment / students by user_id, optionally can set period ( today | monthly| yearly )
	 *
	 * Optionally can set start date & end date to get enrollment list from date range
	 *
	 * If period or date range not pass then it will return all time enrollment list
	 *
	 * @param $user_id int | required
	 *
	 * @param $period string | optional
	 *
	 * @param $start_date string | optional | yy-mm-dd
	 *
	 * @param $end_date string | optional | yy-mm-dd
	 *
	 * @return array
	 *
	 * @since 1.9.9
	 */
	public static function get_total_students_by_user( int $user_id, string $period = '', $start_date = '', string $end_date = '', int $course_id = null ): array {
		global $wpdb;

		$course_post_type = tutor()->course_post_type;
		$user_id          = sanitize_text_field( $user_id );
		$period           = sanitize_text_field( $period );
		$start_date       = sanitize_text_field( $start_date );
		$end_date         = sanitize_text_field( $end_date );

		empty( $period ) ? $period = 'last30days' : 0;
		$period_query              = '';
		$group_query               = ' GROUP BY DATE(date_format) ';

		// set additional query for period or date range
		if ( '' !== $period ) {
			switch ( $period ) {
				case 'today':
					$period_query = ' AND  DATE(enrollment.post_date) = CURDATE() ';
					break;
				case 'monthly':
					$period_query = ' AND  MONTH(enrollment.post_date) = MONTH(CURDATE()) ';
					break;
				case 'yearly':
					$period_query = ' AND  YEAR(enrollment.post_date) = YEAR(CURDATE()) ';
					break;
				case 'last30days':
					$period_query = ' AND  DATE(enrollment.post_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE() ';
					break;
				case 'last90days':
					$period_query = ' AND  DATE(enrollment.post_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND CURDATE() ';
					break;
				case 'last365days':
					$period_query = ' AND  DATE(enrollment.post_date) BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY) AND CURDATE() ';
					break;
				default:
					break;
			}
		}
		// Period query.
		if ( 'today' !== $period ) {
			$group_query = ' GROUP BY MONTH(date_format) ';
		}

		if ( '' !== $start_date and '' !== $end_date ) {
			$period_query = " AND  DATE(enrollment.post_date) BETWEEN CAST('$start_date' AS DATE) AND CAST('$end_date' AS DATE) ";
			$group_query  = ' GROUP BY DATE(date_format) ';
		}

		/**
		 * Author query added to use same query for admin as well
		 * pass user_id value 0 to get all data for admin
		 *
		 * @since v2.0.0
		 */
		$author_query = '';
		if ( $user_id ) {
			$author_query = "AND course.post_author = {$user_id}";
		}

		$course_query = '';
		if ( $course_id ) {
			$course_query = " AND course.ID = {$course_id} ";
		}
		$enrollments       = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT COUNT(enrollment.ID) AS total, enrollment.post_date AS date_format
			FROM 	{$wpdb->posts} enrollment
					INNER  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
					{$course_query}
					{$author_query}
					{$period_query}
					{$group_query}
			",
				$course_post_type,
				'publish',
				'tutor_enrolled',
				'completed'
			)
		);
		$total_enrollments = 0;
		foreach ( $enrollments as $enroll ) {
			$total_enrollments += $enroll->total;
		}
		return array(
			'total_enrollments' => $total_enrollments,
			'enrollments'       => $enrollments,
		);
	}

	/**
	 * Get total refunds by user_id (instructor), optionally can set period ( today | monthly| yearly )
	 *
	 * Optionally can set start date & end date to get enrollment list from date range
	 *
	 * If period or date range not pass then it will return all time enrollment list
	 *
	 * @since 1.9.9
	 *
	 * @since 3.0.0
	 * If monetization is tutor get data from OrderModel
	 *
	 * @param int    $user_id User id, if user not have admin access
	 * then only this user's refund amount will fetched.
	 * @param string $period Time period.
	 * @param string $start_date Start date.
	 * @param string $end_date End date.
	 * @param int    $course_id Course id.
	 *
	 * @return array
	 */
	public static function get_refunds_by_user( int $user_id, string $period = '', $start_date = '', string $end_date = '', int $course_id = null ): array {
		global $wpdb;

		if ( tutor_utils()->is_monetize_by_tutor() ) {
			return ( new OrderController( false ) )->get_refund_data( $user_id, $period, $start_date, $end_date, $course_id );
		}

		$course_post_type = tutor()->course_post_type;
		$user_id          = sanitize_text_field( $user_id );
		$period           = sanitize_text_field( $period );
		$start_date       = sanitize_text_field( $start_date );
		$end_date         = sanitize_text_field( $end_date );

		$course_query = '';
		$period_query = '';
		$group_query  = ' GROUP BY DATE(order_details.date_created) ';
		// set additional query for period or date range.
		if ( '' !== $period ) {
			$period_query = QueryHelper::get_period_clause( 'order_details.date_created', $period );
		}

		// period query.
		if ( 'today' !== $period ) {
			$group_query = ' GROUP BY MONTH(order_details.date_created) ';
		}

		if ( '' !== $start_date && '' !== $end_date ) {
			$period_query = " AND  DATE(order_details.date_created) BETWEEN CAST('$start_date' AS DATE) AND CAST('$end_date' AS DATE) ";
			$group_query  = ' GROUP BY DATE(order_details.date_created) ';
		}

		if ( $course_id ) {
			$course_query = " AND post.ID = $course_id ";
		}

		/**
		 * Author query added to use same query for admin as well
		 * pass user_id value 0 to get all data for admin
		 *
		 * @since v2.0.0
		 */
		$author_query = '';
		if ( $user_id ) {
			$author_query = "AND post.post_author = {$user_id}";
		}

		/**
		 * If admin side then deduct instructor commission
		 * If front side then deduct admin commission
		 *
		 * @since v2.0.9
		 */
		$commission = (int) tutor_utils()->get_option( is_admin() ? 'earning_instructor_commission' : 'earning_admin_commission' );

		$refunds       = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT (SUM(order_details.total_sales) - SUM(order_details.total_sales) * $commission / 100 ) AS total, order_details.date_created AS date_format
				FROM {$wpdb->posts} AS post
					INNER JOIN {$wpdb->postmeta} as mt1 ON mt1.post_id = post.ID
					INNER JOIN {$wpdb->prefix}wc_order_product_lookup AS w_order ON w_order.product_id = mt1.meta_value
					INNER JOIN {$wpdb->prefix}wc_order_stats AS order_details ON order_details.order_id = w_order.order_id
				WHERE mt1.meta_key = %s
					AND post.post_type = %s
					AND post.post_status = %s
					AND order_details.status = %s
					{$author_query}
					{$course_query}
					{$period_query}
					{$group_query}",
				'_tutor_course_product_id',
				$course_post_type,
				'publish',
				'wc-refunded'
			)
		);
		$total_refunds = 0;

		foreach ( $refunds as $refund ) {
			$total_refunds += $refund->total;
		}

		$response = array(
			'refunds'       => $refunds,
			'total_refunds' => $total_refunds,
		);

		return apply_filters( 'tutor_refund_data', $response, $user_id, $period, $start_date, $end_date, $course_id );
	}

	/**
	 * Get total discounts by user_id (instructor), optionally can set period ( today | monthly| yearly )
	 *
	 * Optionally can set start date & end date to get enrollment list from date range
	 *
	 * If period or date range not pass then it will return all time enrollment list
	 *
	 * @param $user_id int | required
	 *
	 * @param $period string | optional
	 *
	 * @param $start_date string | optional | yy-mm-dd
	 *
	 * @param $end_date string | optional | yy-mm-dd
	 *
	 * @return array
	 *
	 * @since 1.9.9
	 */
	public static function get_discounts_by_user( int $user_id, string $period = '', string $start_date = '', string $end_date = '', int $course_id = null ): array {
		global $wpdb;

		if ( tutor_utils()->is_monetize_by_tutor() ) {
			return ( new OrderController( false ) )->get_discount_data( $user_id, $period, $start_date, $end_date, $course_id );
		}

		$course_post_type = tutor()->course_post_type;
		$user_id          = sanitize_text_field( $user_id );
		$period           = sanitize_text_field( $period );
		$start_date       = sanitize_text_field( $start_date );
		$end_date         = sanitize_text_field( $end_date );

		$period_query = '';
		$group_query  = ' GROUP BY order_details.date_created ';
		$course_query = '';
		// set additional query for period or date range
		if ( '' !== $period ) {
			switch ( $period ) {
				case 'today':
					$period_query = ' AND  DATE(order_details.date_created) = CURDATE() ';
					break;
				case 'monthly':
					$period_query = ' AND  MONTH(order_details.date_created) = MONTH(CURDATE()) ';
					break;
				case 'yearly':
					$period_query = ' AND  YEAR(order_details.date_created) = YEAR(CURDATE()) ';
					break;
				case 'last30days':
					$period_query = ' AND  DATE(order_details.date_created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE() ';
					break;
				case 'last90days':
					$period_query = ' AND  DATE(order_details.date_created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND CURDATE() ';
					break;
				case 'last365days':
					$period_query = ' AND  DATE(order_details.date_created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY) AND CURDATE() ';
					break;
				default:
					break;
			}
		}

		// period query
		if ( 'today' !== $period ) {
			$group_query = ' GROUP BY MONTH(order_details.date_created) ';
		}

		if ( $course_id ) {
			$course_query = " AND  post.ID = $course_id ";
		}

		if ( '' !== $start_date and '' !== $end_date ) {
			$period_query = " AND  DATE(order_details.date_created) BETWEEN CAST('$start_date' AS DATE) AND CAST('$end_date' AS DATE) ";
			$group_query  = ' GROUP BY DATE(order_details.date_created) ';
		}

		/**
		 * Author query added to use same query for admin as well
		 * pass user_id value 0 to get all data for admin
		 *
		 * @since v2.0.0
		 */
		$author_query = '';
		if ( $user_id ) {
			$author_query = "AND post.post_author = {$user_id}";
		}

		$discounts       = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT SUM(w_order.coupon_amount) AS total, order_details.date_created AS date_format
				FROM {$wpdb->posts} AS post
					INNER JOIN {$wpdb->postmeta} as mt1 ON mt1.post_id = post.ID
					INNER JOIN {$wpdb->prefix}wc_order_product_lookup AS w_order ON w_order.product_id = mt1.meta_value
					INNER JOIN {$wpdb->prefix}wc_order_stats AS order_details ON order_details.order_id = w_order.order_id
				WHERE  mt1.meta_key = %s
					AND post.post_type = %s
					AND post.post_status = %s
					AND order_details.status = %s
					{$author_query}
					{$course_query}
					{$period_query}
					{$group_query}",
				'_tutor_course_product_id',
				$course_post_type,
				'publish',
				'wc-completed'
			)
		);
		$total_discounts = 0;

		foreach ( $discounts as $discount ) {
			$total_discounts += $discount->total;
		}

		return array(
			'discounts'       => $discounts,
			'total_discounts' => $total_discounts,
		);
	}

	/**
	 * Get total number of sales by user_id (instructor), optionally can set period ( today | monthly| yearly )
	 *
	 * Optionally can set start date & end date to get enrollment list from date range
	 *
	 * If period or date range not pass then it will return all time enrollment list
	 *
	 * @param $user_id int | required
	 *
	 * @param $period string | optional
	 *
	 * @param $start_date string | optional | yy-mm-dd
	 *
	 * @param $end_date string | optional | yy-mm-dd
	 *
	 * @return array
	 *
	 * @since 1.9.9
	 */
	public static function number_of_sales( int $user_id, string $period, string $start_date, string $end_date ): array {
		global $wpdb;

		$user_id    = sanitize_text_field( $user_id );
		$period     = sanitize_text_field( $period );
		$start_date = sanitize_text_field( $start_date );
		$end_date   = sanitize_text_field( $end_date );

		$period_query = '';
		$group_query  = ' GROUP BY DATE(date_format) ';
		$course_query = '';
		// set additional query for period or date range
		if ( '' !== $period ) {
			if ( 'today' === $period ) {
				$period_query = ' AND  DATE(created_at) = CURDATE() ';
			} elseif ( 'monthly' === $period ) {
				$period_query = ' AND  MONTH(created_at) = MONTH(CURDATE()) ';
			} else {
				$period_query = ' AND  YEAR(created_at) = YEAR(CURDATE()) ';
				$group_query  = ' GROUP BY MONTH(date_format) ';
			}
		}

		// period query
		if ( '' === $period || 'yearly' === $period ) {
			$group_query = ' GROUP BY MONTH(date_format) ';
		}

		if ( '' !== $start_date and '' !== $end_date ) {
			$period_query = " AND  DATE(created_at) BETWEEN CAST('$start_date' AS DATE) AND CAST('$end_date' AS DATE) ";
			$group_query  = ' GROUP BY DATE(date_format) ';
		}

		$complete_status = tutor_utils()->get_earnings_completed_statuses();
		$complete_status = "'" . implode( "','", $complete_status ) . "'";

		$sales = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT COUNT(*) AS total, DATE(created_at) AS date_format
            	FROM {$wpdb->prefix}tutor_earnings
            	WHERE 	user_id = %d
					AND order_status IN({$complete_status})
					{$period_query}
					{$group_query}
			",
				$user_id
			)
		);

		// Count total sales
		$total_by_group = array_column( $sales, 'total' );
		$total_sale     = array_sum( $total_by_group );

		return array(
			'sales'       => $sales,
			'total_sales' => $total_sale,
		);
	}

	/**
	 * Get total number of sales by user_id (instructor), optionally can set period ( today | monthly| yearly )
	 *
	 * Optionally can set start date & end date to get enrollment list from date range
	 *
	 * If period or date range not pass then it will return all time enrollment list
	 *
	 * @param $user_id int | required
	 *
	 * @param $period string | optional
	 *
	 * @param $start_date string | optional | yy-mm-dd
	 *
	 * @param $end_date string | optional | yy-mm-dd
	 *
	 * @return array
	 *
	 * @since 1.9.9
	 */
	public static function commission_fees_by_user( int $user_id, string $period, string $start_date, string $end_date ): array {
		global $wpdb;

		$user_id    = sanitize_text_field( $user_id );
		$period     = sanitize_text_field( $period );
		$start_date = sanitize_text_field( $start_date );
		$end_date   = sanitize_text_field( $end_date );

		$period_query = '';
		$group_query  = ' GROUP BY DATE(date_format) ';

		// set additional query for period or date range
		if ( '' !== $period ) {
			if ( 'today' === $period ) {
				$period_query = ' AND  DATE(created_at) = CURDATE() ';
			} elseif ( 'monthly' === $period ) {
				$period_query = ' AND  MONTH(created_at) = MONTH(CURDATE()) ';
			} else {
				$period_query = ' AND  YEAR(created_at) = YEAR(CURDATE()) ';
				$group_query  = ' GROUP BY MONTH(date_format) ';
			}
		}

		// period query
		if ( '' === $period || 'yearly' === $period ) {
			$group_query = ' GROUP BY MONTH(date_format) ';
		}

		if ( '' !== $start_date and '' !== $end_date ) {
			$period_query = " AND  DATE(created_at) BETWEEN CAST('$start_date' AS DATE) AND CAST('$end_date' AS DATE) ";
			$group_query  = ' GROUP BY DATE(date_format) ';
		}

		$complete_status = tutor_utils()->get_earnings_completed_statuses();
		$complete_status = "'" . implode( "','", $complete_status ) . "'";

		$commission_fees   = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT SUM(admin_amount) AS total, SUM(deduct_fees_amount) AS fees, DATE(created_at) AS date_format
            	FROM {$wpdb->prefix}tutor_earnings
            	WHERE 	user_id = %d
					AND order_status IN({$complete_status})
					{$period_query}
					{$group_query}
			",
				$user_id
			)
		);
		$total_commissions = 0;
		$total_fees        = 0;
		$currency_symbol   = function_exists( 'get_woocommerce_currency_symbol' ) ? get_woocommerce_currency_symbol() : '$';
		foreach ( $commission_fees as $cf ) {
			$total_commissions += $cf->total;
			$total_fees        += $cf->fees;
		}
		$total = 0;
		if ( $total_commissions ) {
			$total = $total_commissions . '-' . $currency_symbol . $total_fees;
		}
		return array(
			'commission_fees' => $commission_fees,
			'total'           => $total,
		);
	}

	/**
	 * Get statements user_id (instructor)
	 *
	 * @param $user_id int | required
	 *
	 * @param $offset int | required
	 *
	 * @param $limit int | required
	 *
	 * @param $course_id string | optional
	 *
	 * @param $date_filter string | optional (yy-mm-dd)
	 *
	 * @return array
	 *
	 * @since 1.9.9
	 */
	public static function get_statements_by_user( int $user_id, int $offset, int $limit, $course_id = '', $date_filter = '' ): array {
		global $wpdb;
		$course_post_type = tutor()->course_post_type;
		$bundle_post_type = tutor()->bundle_post_type;

		$user_id     = sanitize_text_field( $user_id );
		$limit       = sanitize_text_field( $limit );
		$offset      = sanitize_text_field( $offset );
		$course_id   = sanitize_text_field( $course_id );
		$date_filter = sanitize_text_field( $date_filter );

		$course_query = '';
		if ( '' !== $course_id ) {
			$course_query = " AND course.ID = $course_id ";
		}

		$date_query = '';
		if ( '' !== $date_filter ) {
			$date_query = " AND DATE(statements.created_at) = CAST( '$date_filter' AS DATE ) ";
		}

		$post_type_in_clause = QueryHelper::prepare_in_clause( array( $course_post_type, $bundle_post_type ) );

		$res = array();

		if ( tutor_utils()->is_monetize_by_tutor() ) {
			$order_model = new OrderModel();
			$res         = $order_model->get_statements( $post_type_in_clause, $course_query, $date_query, $user_id, $offset, $limit );
		} elseif ( 'wc' === tutor_utils()->get_option( 'monetize_by' ) ) {
		//phpcs:disable
		$statements = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT 
				statements.course_price_total AS order_total_price,
				null AS order_tax_amount,
				null AS order_tax_type,
				statements.*, course.post_title AS course_title
				FROM {$wpdb->prefix}tutor_earnings AS statements
					INNER JOIN {$wpdb->posts} AS course ON course.ID = statements.course_id 
					AND course.post_type IN ({$post_type_in_clause})
				WHERE statements.user_id = %d
				{$course_query}
				{$date_query}
				ORDER BY statements.created_at DESC
				LIMIT %d, %d
			",
				$user_id,
				$offset,
				$limit
			)
		);
		//phpcs:enable

			$total_statements = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*)
				FROM {$wpdb->prefix}tutor_earnings AS statements
					INNER JOIN {$wpdb->posts} AS course ON course.ID = statements.course_id
					AND course.post_type IN ({$post_type_in_clause})
				WHERE statements.user_id = %d
				{$course_query}
				{$date_query}
			",
					$user_id
				)
			);

			$res = array(
				'statements'       => $statements,
				'total_statements' => $total_statements,
			);
		}

		return $res;
	}

	/**
	 * Get current user
	 *
	 * @return object
	 *
	 * @since 1.9.8
	 */
	public static function current_user() {
		$user = wp_get_current_user();
		return $user;
	}

	/**
	 * Enqueue styles
	 *
	 * @since 1.9.8
	 */
	public function tutor_analytics_scripts() {
		global $wp_query;
		$query_vars = $wp_query->query_vars;

		if ( isset( $query_vars['tutor_dashboard_page'] ) && $query_vars['tutor_dashboard_page'] == 'analytics' ) {
			$current_page = isset( $query_vars['tutor_dashboard_sub_page'] ) ? $query_vars['tutor_dashboard_sub_page'] : 'overview';

				// enqueue scripts.
				wp_enqueue_script(
					'tutor-pro-chart-js',
					tutor_pro()->url . 'assets/lib/Chart.bundle.min.js',
					array(),
					TUTOR_PRO_VERSION,
					true
				);
				wp_enqueue_script(
					'tutor-pro-analytics',
					TUTOR_REPORT()->url . 'assets/js/analytics.js',
					array( 'jquery', 'tutor-pro-chart-js' ),
					TUTOR_PRO_VERSION,
					true
				);
				wp_add_inline_script(
					'tutor-pro-analytics',
					'const _tutor_analytics = ' . json_encode( self::chart_dependent_data() ),
					'before'
				);

			// export js.
			if ( 'export' === $current_page ) {
				wp_enqueue_script(
					'tutor-pro-jszip',
					tutor_pro()->url . 'assets/lib/jszip.min.js',
					array(),
					TUTOR_PRO_VERSION,
					true
				);
				wp_enqueue_script(
					'tutor-pro-export',
					TUTOR_REPORT()->url . 'assets/js/export.js',
					array( 'jquery', 'tutor-pro-jszip' ),
					TUTOR_PRO_VERSION,
					true
				);
			}

			// enqueue styles
			wp_enqueue_style(
				'tutor-pro-analytics',
				TUTOR_REPORT()->url . 'assets/css/analytics.css',
				'',
				time()
			);

		}
	}

	/**
	 * Get dependent data to make chart
	 *
	 * It will return data as per query vars for Earnings | Enrollment list | Refunds | Discounts
	 *
	 * @return array
	 *
	 * @since 1.9.9
	 */
	protected static function chart_dependent_data() {
		global $wp_query;
		$query_vars = $wp_query->query_vars;
		$user_id    = self::current_user()->ID;
		$analytics  = array();

		if ( isset( $query_vars['tutor_dashboard_page'] ) && 'analytics' === $query_vars['tutor_dashboard_page'] ) {
			$time_period = Input::get( 'period', '' );
			$start_date  = Input::get( 'start_date', '' );
			$end_date    = Input::get( 'end_date', '' );
			if ( '' !== $start_date ) {
				$start_date = tutor_get_formated_date( 'Y-m-d', $start_date );
			}
			if ( '' !== $end_date ) {
				$end_date = tutor_get_formated_date( 'Y-m-d', $end_date );
			}

			if ( ! isset( $query_vars['tutor_dashboard_sub_page'] ) || ( isset( $query_vars['tutor_dashboard_sub_page'] ) && $query_vars['tutor_dashboard_sub_page'] === 'overview' ) ) {
				$analytics = array(
					array(
						'id'    => 'ta_total_earnings',
						'label' => __( 'Earning', 'tutor-pro' ),
						'data'  => self::get_earnings_by_user( $user_id, $time_period, $start_date, $end_date )['earnings'],
					),
					array(
						'id'    => 'ta_total_course_enrolled',
						'label' => __( 'Enrolled', 'tutor-pro' ),
						'data'  => self::get_total_students_by_user( $user_id, $time_period, $start_date, $end_date )['enrollments'],
					),
					array(
						'id'    => 'ta_total_discount',
						'label' => __( 'Discount', 'tutor-pro' ),
						'data'  => self::get_discounts_by_user( $user_id, $time_period, $start_date, $end_date )['discounts'],
					),
					array(
						'id'    => 'ta_total_refund',
						'label' => __( 'Refund', 'tutor-pro' ),
						'data'  => self::get_refunds_by_user( $user_id, $time_period, $start_date, $end_date )['refunds'],
					),
				);
			}

			// Course details graph.
			if ( isset( $query_vars['tutor_dashboard_sub_page'] ) && 'course-details' === $query_vars['tutor_dashboard_sub_page'] ) {
				$course_id = Input::get( 'course_id', 0, Input::TYPE_INT );
				$analytics = array(
					array(
						'id'    => 'ta_total_earnings',
						'label' => __( 'Total Earning', 'tutor-pro' ),
						'data'  => self::get_earnings_by_user( $user_id, $time_period, $start_date, $end_date, $course_id )['earnings'],
					),
					array(
						'id'    => 'ta_total_discount',
						'label' => __( 'Discount', 'tutor-pro' ),
						'data'  => self::get_discounts_by_user( $user_id, $time_period, $start_date, $end_date, $course_id )['discounts'],
					),
					array(
						'id'    => 'ta_total_refund',
						'label' => __( 'Refund', 'tutor-pro' ),
						'data'  => self::get_refunds_by_user( $user_id, $time_period, $start_date, $end_date, $course_id )['refunds'],
					),
				);
			}

			// Earning graph.
			if ( isset( $query_vars['tutor_dashboard_sub_page'] ) && $query_vars['tutor_dashboard_sub_page'] === 'earnings' ) {
				$analytics = array(
					array(
						'id'    => 'ta_total_earnings',
						'label' => __( 'Total Earning', 'tutor-pro' ),
						'data'  => self::get_earnings_by_user( $user_id, $time_period, $start_date, $end_date )['earnings'],
					),
					array(
						'id'    => 'ta_total_course_enrolled',
						'label' => __( 'Number of Sales', 'tutor-pro' ),
						'data'  => self::number_of_sales( $user_id, $time_period, $start_date, $end_date )['sales'],
					),
					array(
						'id'     => 'ta_total_refund',
						'label'  => __( 'Commission', 'tutor-pro' ),
						'label2' => __( 'Fees', 'tutor-pro' ),
						'data'   => self::commission_fees_by_user( $user_id, $time_period, $start_date, $end_date )['commission_fees'],
					),
				);
			}

			return $analytics;
		}
		return $analytics;
	}

	public function view_progress() {
		tutor_utils()->checking_nonce();
		ob_start();
		include_once TUTOR_REPORT()->path . 'templates/course_progress.php';
		//phpcs:ignore --output esc in template file
		echo ob_get_clean();
		exit;
	}

	/**
	 * Get analytics data for export based on current user
	 *
	 * @since 1.9.10
	 *
	 * @return array
	 */
	public function analytics_data(): array {
		$arr = array(
			'students'  => $this->students_data(),
			'earnings'  => $this->earnings_data(),
			'discounts' => $this->discounts_data(),
			'refunds'   => $this->refunds_data(),
		);
		return $arr;
	}

	public function export_analytics() {
		tutor_utils()->checking_nonce();
		$arr = $this->analytics_data();
		wp_send_json_success( $arr );
	}

	/**
	 * Get last enrolled courses
	 *
	 * @param integer $limit
	 * @return mixed
	 * @since 2.0.2
	 */
	public static function get_last_enrolled_courses( int $limit = 5 ) {
		global $wpdb;

		$sql = "SELECT MAX(enrolled.post_date) as enrolled_time, enrolled.guid, enrolled.post_parent, course.ID, course.post_title
		FROM {$wpdb->posts} enrolled
		LEFT JOIN {$wpdb->posts} course ON enrolled.post_parent = course.ID
		WHERE enrolled.post_type = %s 
		AND enrolled.post_status = %s 
		AND course.post_type = %s
		GROUP BY enrolled.post_parent
		ORDER BY enrolled_time DESC LIMIT 0,%d ;";

		return $wpdb->get_results( $wpdb->prepare( $sql, 'tutor_enrolled', 'completed', tutor()->course_post_type, $limit ) );
	}

	/**
	 * Get reviews
	 *
	 * @param integer $limit
	 * @return mixed
	 * @since 2.0.2
	 */
	public static function get_reviews( int $limit = 5 ) {
		global $wpdb;

		$sql = "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
		AND meta_key = %s 
		ORDER BY comment_ID DESC LIMIT 0,%d ;";

		return $wpdb->get_results( $wpdb->prepare( $sql, 'tutor_rating', $limit ) );
	}

	/**
	 * Get students
	 *
	 * @param integer $limit
	 * @return mixed
	 * @since 2.0.2
	 */
	public static function get_students( int $limit = 5 ) {
		global $wpdb;

		$sql = "SELECT SQL_CALC_FOUND_ROWS {$wpdb->users}.* ,
		{$wpdb->usermeta}.meta_value as registered_timestamp
		FROM {$wpdb->users}
		INNER JOIN {$wpdb->usermeta}
		ON ( {$wpdb->users}.ID = {$wpdb->usermeta}.user_id )
		WHERE 1 = %d
		AND ( {$wpdb->usermeta}.meta_key = %s )
		ORDER BY {$wpdb->usermeta}.meta_value DESC
		LIMIT 0,%d ";

		return $wpdb->get_results( $wpdb->prepare( $sql, 1, '_is_tutor_student', $limit ) );
	}

	/**
	 * Get teachers
	 *
	 * @param integer $limit
	 * @return mixed
	 * @since 2.0.2
	 */
	public static function get_teachers( int $limit = 5 ) {
		global $wpdb;

		$sql = "SELECT SQL_CALC_FOUND_ROWS {$wpdb->users}.* , meta_role.meta_value as registered_timestamp
		FROM {$wpdb->users}
		INNER JOIN {$wpdb->usermeta} meta_role ON ( {$wpdb->users}.ID = meta_role.user_id )
		INNER JOIN {$wpdb->usermeta} meta_status ON ( {$wpdb->users}.ID = meta_status.user_id )
		WHERE meta_role.meta_key = %s
			AND meta_status.meta_key = %s
			AND meta_status.meta_value = %s
		ORDER BY meta_role.meta_value DESC
		LIMIT 0,%d ";

		return $wpdb->get_results( $wpdb->prepare( $sql, '_is_tutor_instructor', '_tutor_instructor_status', 'approved', $limit ) );
	}
}
