<?php

/**
 * Tutor Course attachments Main Class
 */

namespace TUTOR_REPORT;

use TUTOR\Backend_Page_Trait;
use \TUTOR_REPORT\Analytics;
use TUTOR\Tutor_Base;

class Report extends Tutor_Base
{

	/**
	 * Backend_Page_Trait for inherit common methods
	 * ex: Bulk actions
	 */
	use Backend_Page_Trait;

	public function __construct()
	{
		parent::__construct();

		add_action('admin_enqueue_scripts', array($this, 'admin_scripts'));
		add_action('tutor_admin_register', array($this, 'register_menu'));

		/**
		 * Ajax Action
		 */
		add_action('wp_ajax_treport_quiz_atttempt_delete', array($this, 'treport_quiz_atttempt_delete'));

		//Download CSV
		add_action('admin_init', array($this, 'download_course_enrol_csv'));

		add_action('admin_enqueue_scripts', array($this, 'load_backend_scripts'));

		/**
		 * Handle ajax request for total enrolment graph for backend
		 *
		 * @since v.2.0.0
		 */
		add_action('wp_ajax_tutor_report_get_student_enrollments', array(__CLASS__, 'total_enrolled_students'));

		/**
		 * Handle ajax request for bulk action
		 *
		 * @since v.2.0.0
		 */
		add_action('wp_ajax_tutor_admin_student_list_bulk_action', array($this, 'bulk_action'));
	}

	public function admin_scripts($page)
	{
		/**
		 * Scripts
		 */
		if ($page === 'tutor-lms-pro_page_tutor_report') {
			//wp_enqueue_style('tutor-report', TUTOR_REPORT()->url.'assets/css/report.css', array(), TUTOR_REPORT()->version);
			wp_enqueue_script('tutor-cahrt-js', TUTOR_REPORT()->url . 'assets/js/lib/Chart.bundle.min.js', array(), TUTOR_REPORT()->version);
			wp_enqueue_script('tutor-report', TUTOR_REPORT()->url . 'assets/js/report.js', array('tutor-admin'), TUTOR_REPORT()->version, true);
		}
	}

	public function register_menu()
	{
		add_submenu_page('tutor', __('Reports', 'tutor-pro'), __('Reports', 'tutor-pro'), 'manage_tutor', 'tutor_report', array($this, 'tutor_report'));
	}

	public function tutor_report()
	{
		include TUTOR_REPORT()->path . 'views/pages/report.php';
	}

	public function treport_quiz_atttempt_delete()
	{
		tutor_utils()->checking_nonce();

		global $wpdb;

		$attempt_id = (int) sanitize_text_field($_POST['attempt_id']);

		$wpdb->delete($wpdb->comments, array('comment_ID' => $attempt_id));
		$wpdb->delete($wpdb->commentmeta, array('comment_id' => $attempt_id));

		wp_send_json_success();
	}

	public function download_course_enrol_csv()
	{
		if (empty($_GET['tutor_report_action']) || $_GET['tutor_report_action'] !== 'download_course_enrol_csv') {
			return;
		}
		global $wpdb;

		$time_period = 'this_year';
		$course_id = false;
		if (!empty($_GET['time_period'])) {
			$time_period = sanitize_text_field($_GET['time_period']);
		}
		if (!empty($_GET['course_id'])) {
			$course_id = (int) sanitize_text_field($_GET['course_id']);
		}
		if (!empty($_GET['date_range_from']) && !empty($_GET['date_range_to'])) {
			$time_period = 'date_range';
		}

		$chartData = array();

		$single_course_query = '';
		if (!empty($_GET['course_id'])) {
			$course_id = (int) sanitize_text_field($_GET['course_id']);
			if ($course_id) {
				$single_course_query = "AND post_parent = {$course_id}";
			}
		}

		switch ($time_period) {
			case 'this_year';

				$currentYear = date('Y');

				$enrolledQuery = $wpdb->get_results("
	              SELECT COUNT(ID) as total_enrolled, 
	              MONTHNAME(post_date)  as month_name 
	              from {$wpdb->posts} 
	              WHERE post_type = 'tutor_enrolled' 
	              AND YEAR(post_date) = {$currentYear} 
	              {$single_course_query}
	              GROUP BY MONTH (post_date) 
	              ORDER BY MONTH(post_date) ASC ;");

				$total_enrolled = wp_list_pluck($enrolledQuery, 'total_enrolled');
				$months = wp_list_pluck($enrolledQuery, 'month_name');
				$monthWiseEnrolled = array_combine($months, $total_enrolled);

				$emptyMonths = array();
				for ($m = 1; $m <= 12; $m++) {
					$emptyMonths[date('F', mktime(0, 0, 0, $m, 1, date('Y')))] = 0;
				}
				$chartData = array_merge($emptyMonths, $monthWiseEnrolled);

				break;
			case 'last_year';

				$lastYear = date('Y', strtotime('-1 year'));

				$enrolledQuery = $wpdb->get_results("
	              SELECT COUNT(ID) as total_enrolled, 
	              MONTHNAME(post_date)  as month_name 
	              from {$wpdb->posts} 
	              WHERE post_type = 'tutor_enrolled' 
	              AND YEAR(post_date) = {$lastYear} 
	              {$single_course_query}
	              GROUP BY MONTH (post_date) 
	              ORDER BY MONTH(post_date) ASC ;");

				$total_enrolled = wp_list_pluck($enrolledQuery, 'total_enrolled');
				$months = wp_list_pluck($enrolledQuery, 'month_name');
				$monthWiseEnrolled = array_combine($months, $total_enrolled);

				$emptyMonths = array();
				for ($m = 1; $m <= 12; $m++) {
					$emptyMonths[date('F', mktime(0, 0, 0, $m, 1, date('Y')))] = 0;
				}
				$chartData = array_merge($emptyMonths, $monthWiseEnrolled);

				break;
			case 'last_month';

				$start_date = date("Y-m", strtotime('-1 month'));
				$start_date = $start_date . '-1';
				$end_date = date("Y-m-t", strtotime($start_date));

				/**
				 * Format Date Name
				 */
				$begin = new \DateTime($start_date);
				$end = new \DateTime($end_date . ' + 1 day');
				$interval = \DateInterval::createFromDateString('1 day');
				$period = new \DatePeriod($begin, $interval, $end);

				$datesPeriod = array();
				foreach ($period as $dt) {
					$datesPeriod[$dt->format("Y-m-d")] = 0;
				}

				$enrolledQuery = $wpdb->get_results("
	              SELECT COUNT(ID) as total_enrolled, 
	              DATE(post_date)  as date_format 
	              from {$wpdb->posts} 
	              WHERE post_type = 'tutor_enrolled' 
	              AND (post_date BETWEEN '{$start_date}' AND '{$end_date}')
	              {$single_course_query}
	              GROUP BY date_format
	              ORDER BY post_date ASC ;");

				$total_enrolled = wp_list_pluck($enrolledQuery, 'total_enrolled');
				$queried_date = wp_list_pluck($enrolledQuery, 'date_format');
				$dateWiseEnrolled = array_combine($queried_date, $total_enrolled);

				$chartData = array_merge($datesPeriod, $dateWiseEnrolled);
				foreach ($chartData as $key => $enrolledCount) {
					unset($chartData[$key]);
					$formatDate = date('d M', strtotime($key));
					$chartData[$formatDate] = $enrolledCount;
				}

				break;
			case 'this_month';

				$start_week = date("Y-m-01");
				$end_week = date("Y-m-t");

				/**
				 * Format Date Name
				 */
				$begin = new \DateTime($start_week);
				$end = new \DateTime($end_week . ' + 1 day');
				$interval = \DateInterval::createFromDateString('1 day');
				$period = new \DatePeriod($begin, $interval, $end);

				$datesPeriod = array();
				foreach ($period as $dt) {
					$datesPeriod[$dt->format("Y-m-d")] = 0;
				}

				$enrolledQuery = $wpdb->get_results("
	              SELECT COUNT(ID) as total_enrolled, 
	              DATE(post_date)  as date_format 
	              from {$wpdb->posts} 
	              WHERE post_type = 'tutor_enrolled' 
	              AND (post_date BETWEEN '{$start_week}' AND '{$end_week}')
	              {$single_course_query}
	              GROUP BY date_format
	              ORDER BY post_date ASC ;");

				$total_enrolled = wp_list_pluck($enrolledQuery, 'total_enrolled');
				$queried_date = wp_list_pluck($enrolledQuery, 'date_format');
				$dateWiseEnrolled = array_combine($queried_date, $total_enrolled);

				$chartData = array_merge($datesPeriod, $dateWiseEnrolled);
				foreach ($chartData as $key => $enrolledCount) {
					unset($chartData[$key]);
					$formatDate = date('d M', strtotime($key));
					$chartData[$formatDate] = $enrolledCount;
				}

				break;
			case 'last_week';

				$previous_week = strtotime("-1 week +1 day");
				$start_week = strtotime("last sunday midnight", $previous_week);
				$end_week = strtotime("next saturday", $start_week);
				$start_week = date("Y-m-d", $start_week);
				$end_week = date("Y-m-d", $end_week);

				/**
				 * Format Date Name
				 */
				$begin = new \DateTime($start_week);
				$end = new \DateTime($end_week . ' + 1 day');
				$interval = \DateInterval::createFromDateString('1 day');
				$period = new \DatePeriod($begin, $interval, $end);

				$datesPeriod = array();
				foreach ($period as $dt) {
					$datesPeriod[$dt->format("Y-m-d")] = 0;
				}

				$enrolledQuery = $wpdb->get_results("
	              SELECT COUNT(ID) as total_enrolled, 
	              DATE(post_date)  as date_format 
	              from {$wpdb->posts} 
	              WHERE post_type = 'tutor_enrolled' 
	              AND (post_date BETWEEN '{$start_week}' AND '{$end_week}')
	              {$single_course_query}
	              GROUP BY date_format
	              ORDER BY post_date ASC ;");

				$total_enrolled = wp_list_pluck($enrolledQuery, 'total_enrolled');
				$queried_date = wp_list_pluck($enrolledQuery, 'date_format');
				$dateWiseEnrolled = array_combine($queried_date, $total_enrolled);

				$chartData = array_merge($datesPeriod, $dateWiseEnrolled);
				foreach ($chartData as $key => $enrolledCount) {
					unset($chartData[$key]);
					$formatDate = date('d M', strtotime($key));
					$chartData[$formatDate] = $enrolledCount;
				}


				break;
			case 'this_week';

				$start_week = date("Y-m-d", strtotime("last sunday midnight"));
				$end_week = date("Y-m-d", strtotime("next saturday"));
				/**
				 * Format Date Name
				 */
				$begin = new \DateTime($start_week);
				$end = new \DateTime($end_week . ' + 1 day');
				$interval = \DateInterval::createFromDateString('1 day');
				$period = new \DatePeriod($begin, $interval, $end);

				$datesPeriod = array();
				foreach ($period as $dt) {
					$datesPeriod[$dt->format("Y-m-d")] = 0;
				}

				$enrolledQuery = $wpdb->get_results("
	              SELECT COUNT(ID) as total_enrolled, 
	              DATE(post_date)  as date_format 
	              from {$wpdb->posts} 
	              WHERE post_type = 'tutor_enrolled' 
	              AND (post_date BETWEEN '{$start_week}' AND '{$end_week}')
	              {$single_course_query}
	              GROUP BY date_format
	              ORDER BY post_date ASC ;");

				$total_enrolled = wp_list_pluck($enrolledQuery, 'total_enrolled');
				$queried_date = wp_list_pluck($enrolledQuery, 'date_format');
				$dateWiseEnrolled = array_combine($queried_date, $total_enrolled);

				$chartData = array_merge($datesPeriod, $dateWiseEnrolled);
				foreach ($chartData as $key => $enrolledCount) {
					unset($chartData[$key]);
					$formatDate = date('d M', strtotime($key));
					$chartData[$formatDate] = $enrolledCount;
				}

				break;
			case 'date_range';

				$start_week = sanitize_text_field($_GET['date_range_from']);
				$end_week = sanitize_text_field($_GET['date_range_to']);

				/**
				 * Format Date Name
				 */
				$begin = new \DateTime($start_week);
				$end = new \DateTime($end_week . ' + 1 day');
				$interval = \DateInterval::createFromDateString('1 day');
				$period = new \DatePeriod($begin, $interval, $end);

				$datesPeriod = array();
				foreach ($period as $dt) {
					$datesPeriod[$dt->format("Y-m-d")] = 0;
				}

				$enrolledQuery = $wpdb->get_results("
	              SELECT COUNT(ID) as total_enrolled, 
	              DATE(post_date)  as date_format 
	              from {$wpdb->posts} 
	              WHERE post_type = 'tutor_enrolled' 
	              AND (post_date BETWEEN '{$start_week}' AND '{$end_week}')
	              {$single_course_query}
	              GROUP BY date_format
	              ORDER BY post_date ASC ;");

				$total_enrolled = wp_list_pluck($enrolledQuery, 'total_enrolled');
				$queried_date = wp_list_pluck($enrolledQuery, 'date_format');
				$dateWiseEnrolled = array_combine($queried_date, $total_enrolled);

				$chartData = array_merge($datesPeriod, $dateWiseEnrolled);
				foreach ($chartData as $key => $enrolledCount) {
					unset($chartData[$key]);
					$formatDate = date('d M', strtotime($key));
					$chartData[$formatDate] = $enrolledCount;
				}
				break;
		}

		$this->download_send_headers("tutor_report_course_enroll_" . date("Y-m-d") . ".csv");

		ob_start();
		$df = fopen("php://output", 'w');
		fputcsv($df, array_keys($chartData));
		fputcsv($df, $chartData);
		fclose($df);
		echo ob_get_clean();
		die();
	}

	public function download_send_headers($filename)
	{
		// disable caching
		$now = gmdate("D, d M Y H:i:s");
		header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
		header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
		header("Last-Modified: {$now} GMT");

		// force download
		header("Content-Type: application/force-download");
		header("Content-Type: application/octet-stream");
		header("Content-Type: application/download");

		// disposition / encoding on response body
		header("Content-Disposition: attachment;filename={$filename}");
		header("Content-Transfer-Encoding: binary");
	}

	/**
	 * Get total enrolled students
	 * handle ajax request
	 * 
	 * @since v2.0.0
	 */
	public static function total_enrolled_students()
	{
		$period 	= isset($_POST['period']) ? $_POST['period'] : '';
		$start_date = isset($_POST['start_date']) && '' !== $_POST['start_date'] ? tutor_get_formated_date('Y-m-d', $_POST['start_date']) : '';
		$end_date 	= isset($_POST['end_date']) && '' !== $_POST['end_date'] ? tutor_get_formated_date('Y-m-d', $_POST['end_date']) : '';

		$enrollments = Analytics::get_total_students_by_user(0, $period, $start_date, $end_date);
		wp_send_json($enrollments);
		exit;
	}

	/**
	 * Backend report css
	 *
	 * @since v2.0.0
	 */
	public function load_backend_scripts()
	{

		if (isset($_GET['page']) && $_GET['page'] === 'tutor_report') {
			wp_enqueue_style(
				'tutor-pro-analytics',
				TUTOR_REPORT()->url . 'assets/css/analytics.css',
				'',
				TUTOR_PRO_VERSION
			);
			wp_enqueue_style(
				'tutor-pro-report',
				TUTOR_REPORT()->url . 'assets/css/report.css',
				'',
				TUTOR_PRO_VERSION
			);

			wp_enqueue_script(
				'tutor-pro-analytics',
				TUTOR_REPORT()->url . 'assets/js/analytics.js',
				array('jquery'),
				TUTOR_PRO_VERSION,
				true
			);

			wp_add_inline_script(
				'tutor-pro-analytics',
				'const _tutor_analytics = ' . json_encode(self::chart_dependent_data()),
				'before'
			);
		}
	}

	/**
	 * Get chart data for admin overview page
	 *
	 * @since v2.0.0
	 * @return array
	 */
	public static function chart_dependent_data(): array
	{
		$time_period = isset($_GET['period']) ? sanitize_text_field($_GET['period']) : '';
		$start_date  = isset($_GET['start_date']) ? sanitize_text_field($_GET['start_date']) : '';
		$end_date 	 = isset($_GET['end_date']) ? sanitize_text_field($_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);
		}
		$current_page 	= isset($_GET['page']) ? $_GET['page'] : '';
		$sub_page 		= isset($_GET['sub_page']) ? $_GET['sub_page'] : 'overview';

		/**
		 * If the sub_page is overview or it is course details page (admin side)
		 * then get graph data
		 *
		 * @since v2.0.0
		 */
		$course_id = isset($_GET['course_id']) ? $_GET['course_id'] : null;
		if ('overview' === $sub_page || ('courses' === $sub_page && '' != $course_id)) {
			$overview_graph = array(
				array(
					'id'	=> 'ta_total_earnings',
					'label'	=> __('Earning', 'tutor-pro'),
					'data'	=> Analytics::get_earnings_by_user(0, $time_period, $start_date, $end_date, $course_id)['earnings']
				),
				array(
					'id'	=> 'ta_total_course_enrolled',
					'label'	=> __('Enrolled', 'tutor-pro'),
					'data'	=> Analytics::get_total_students_by_user(0, $time_period, $start_date, $end_date, $course_id)['enrollments']
				),
				array(
					'id'	=> 'ta_total_refund',
					'label'	=> __('Refund', 'tutor-pro'),
					'data'	=> Analytics::get_refunds_by_user(0, $time_period, $start_date, $end_date, $course_id)['refunds']
				),
				array(
					'id'	=> 'ta_total_discount',
					'label'	=> __('Discount', 'tutor-pro'),
					'data'	=> Analytics::get_discounts_by_user(0, $time_period, $start_date, $end_date, $course_id)['discounts']
				)
			);
			return $overview_graph;
		}
		return array();
	}

	/**
	 * Get sales list
	 *
	 * @param int $offset, to set offset in query | optional.
	 * @param int $limit, to get limited item | optional.
	 * @param string $course_id, to sort item course wise | optional.
	 * @param string $date, to sort item date wise | YYYY-MM-DD | optional.
	 * @param string $search, to sort item as course title | optional.
	 * @since v2.0.0
	 * @return array
	 */
	public static function sales_list(int $offset = 0, int $limit = 10, $course_id = '', $date = '', $order = '', $search = ''): array
	{
		global $wpdb;

		$offset     = sanitize_text_field($offset);
		$limit      = sanitize_text_field($limit);
		$course_id  = sanitize_text_field($course_id);
		$date       = sanitize_text_field($date);
		$order      = sanitize_text_field($order);
		$search 	= sanitize_text_field($search);

		$search_term = '%' . $wpdb->esc_like($search) . '%';

		// Add course id in where clause.
		$course_query = '';
		if ('' !== $course_id) {
			$course_query = "AND course.ID = $course_id";
		}

		// Add date in where clause.
		$date_query = '';
		if ('' !== $date) {
			$date_query = "AND DATE(post.post_date) = CAST('$date' AS DATE) ";
		}

		// Order query
		$order_query = '';
		if ('' !== $order) {
			$order_query = "ORDER BY post.ID {$order}";
		} else {
			$order_query = "ORDER BY post.ID DESC";
		}

		$sales = $wpdb->get_results($wpdb->prepare(
			"SELECT post.ID, post.post_parent, post.post_author, post.post_status, post.post_date, meta.meta_value as order_id 
				FROM {$wpdb->posts} AS post
				INNER JOIN {$wpdb->posts} AS course 
					ON course.ID = post.post_parent
				JOIN {$wpdb->postmeta} meta 
					ON post.ID = meta.post_id
				WHERE meta.meta_key = %s 
					AND post.post_type = %s
					{$course_query}
					{$date_query}
					AND (course.post_title LIKE %s )
				{$order_query}
				LIMIT %d, %d
			",
			'_tutor_enrolled_by_order_id',
			'tutor_enrolled',
			$search_term,
			$offset,
			$limit
		));

		return array(
			'list'	=> $sales,
			'total'	=> self::count_total_sales($course_id, $date, $search)
		);
	}

	/**
	 * Count total sales item
	 *
	 * @since v2.0.0
	 * @return int
	 */
	public static function count_total_sales($course_id = '', $date = '', $search = ''): int
	{
		global $wpdb;
		$course_id  = sanitize_text_field($course_id);
		$date       = sanitize_text_field($date);
		$search 	= sanitize_text_field($search);
		$search_term = '%' . $wpdb->esc_like($search) . '%';

		$course_query = '';
		if ('' !== $course_id) {
			$course_query = "AND course.ID = $course_id";
		}
		// Add date in where clause.
		$date_query = '';
		if ('' !== $date) {
			$date_query = "AND DATE(post.post_date) = CAST('$date' AS DATE) ";
		}

		$total_items = $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*)
				FROM {$wpdb->posts} AS post
				INNER JOIN {$wpdb->posts} AS course 
					ON course.ID = post.post_parent
				JOIN {$wpdb->postmeta} meta 
					ON post.ID = meta.post_id
				WHERE meta.meta_key = %s 
					AND post.post_type = %s
					{$course_query}
					{$date_query}
					AND (course.post_title LIKE %s )
			",
			'_tutor_enrolled_by_order_id',
			'tutor_enrolled',
			$search_term
		));
		return $total_items ? $total_items : 0;
	}

	/**
	 * Get available bulk actions for student list
	 *
	 * @since v.2.0.0
	 * @return array
	 */
	public function student_list_bulk_actions()
	{
		$actions = array(
			$this->bulk_action_default(),
			$this->bulk_action_delete()
		);
		return apply_filters('tutor_admin_student_list_bulk_action', $actions);
	}

	/**
	 * Handle bulk action
	 *
	 * @return json response
	 * @since v2.0.0
	 */
	public function bulk_action()
	{
		tutor_utils()->checking_nonce();
		$bulk_action = isset($_POST['bulk-action']) ? $_POST['bulk-action'] : '';
		$bulk_ids 	 = $_POST['bulk-ids'];

		if ('delete' === $bulk_action) {
			return self::delete_students($bulk_ids);
		}
		exit;
	}

	/**
	 * Delete student
	 *
	 * @param string $student_ids, ids that need to delete.
	 * @param int $reassign_id, reassign to other user.
	 * @return bool
	 * @since v2.0.0
	 */
	public static function delete_students(string $student_ids, $reassign_id = NULL): bool
	{
		$student_ids = explode(',', $student_ids);
		foreach ($student_ids as $id) {
			if (NULL === $reassign_id) {
				wp_delete_user($id);
			} else {
				wp_delete_user($id, $reassign_id);
			}
		}
		return true;
	}
}
