<?php
namespace stmLms\Classes\Models;

use STM_LMS_Options;
use stmLms\Classes\Models\StmOrder;
use stmLms\Classes\Models\StmOrderItems;
use stmLms\Classes\Models\Admin\StmStatisticsListTable;

class StmStatistics {

	static $instance;
	public $object;

	public static function set_screen( $status, $option, $value ) {
		return $value;
	}

	public static function init(){
		$model = new StmStatistics();
		self::get_instance();
		add_filter( 'set-screen-option', [ __CLASS__, 'set_screen' ], 10, 3 );
		if(is_admin()){
            self::create_table_order_items();
			add_action('init',  [ self::class, "woocommerce_order_items" ]);
			self::set_order_items_total_price();
		}
	}

	public function admin_menu(){
		add_action('admin_menu', array($this, 'add_order_list'));
	}

	public function add_order_list() {
		$hook = add_submenu_page(
			'stm-lms-settings',
			__( 'Statistics', "masterstudy-lms-learning-management-system"),
			__( 'Statistics', "masterstudy-lms-learning-management-system"),
			'manage_options',
			'stm_lms_statistics',
			array($this, 'render_statistics')
		);
 		add_action( "load-$hook", [ $this, 'stm_lms_statistics_screen_option' ] );
	}

	public function render_statistics() {
		stm_lms_render(STM_LMS_PATH."/lms/views/statistics/statistics", [], true);
	}

	public function stm_lms_statistics_screen_option() {
		$option = 'per_page';
		$args   = [
			'label'   => 'Statistics',
			'default' => 10,
			'option'  => 'stm_lms_statistics_per_page'
		];
		add_screen_option( $option, $args );
		$this->object = new StmStatisticsListTable();
	}

	public static function set_order_items_total_price() {
		$is_run = get_option("stm_lms_set_order_total_price");
		if(!$is_run) {
			global $wpdb;
			$prefix = $wpdb->prefix;
			$orders = StmOrder::query()
			                  ->select(" _order.*, mete.`meta_value` as items ")
			                  ->asTable("_order")
			                  ->join(" left join ".$prefix."postmeta as mete on (mete.post_id = _order.ID)  ")
			                  ->where_in("_order.post_type", ["stm-orders"])
			                  ->where("mete.`meta_key`", "items")
			                  ->group_by("_order.ID")
			                  ->find();
			foreach ($orders as $order) {
				$total_price = 0;
				foreach ($order->items as $item){
					$total_price+= $item['price'];

					// update or create order items
					if( !($order_items = StmOrderItems::query()->where("order_id", $order->ID)->where("object_id", $item['item_id'])->findOne()) )
						$order_items = new StmOrderItems();
					$order_items->order_id = $order->ID;
					$order_items->object_id = $item['item_id'];
					$order_items->price = $item['price'];
					$order_items->quantity = 1;
					$order_items->transaction = 0;
					$order_items->save();
				}
				update_post_meta($order->ID, "_order_total", $total_price);
			}
			add_option("stm_lms_set_order_total_price", "1");
		}
	}

	public static function woocommerce_order_items(){
		$is_run = get_option("stm_lms_set_woocommerce_order_items");
		if(!$is_run) {
			global $wpdb;
			$prefix = $wpdb->prefix;
			$orders = StmOrder::query()
			                  ->select(" _order.*, meta.meta_value as items")
			                  ->asTable("_order")
			                  ->join(" left join ".$prefix."postmeta as meta on ( meta.`post_id` = _order.ID AND meta.`meta_key` = 'stm_lms_courses') ")
			                  ->where("_order.`post_type`", "shop_order")
			                  ->find();
			foreach ($orders as $order){
				if(isset($order->items)) {
					foreach ($order->items as $item){
						$price = get_post_meta($item['item_id'], "_price");

						// update or create order items
						if( !($order_items = StmOrderItems::query()->where("order_id", $order->ID)->where("object_id", $item['item_id'])->findOne()) )
							$order_items = new StmOrderItems();
						$order_items->order_id    = $order->ID;
						$order_items->object_id   = $item['item_id'];
						$order_items->price       = (isset($price[0])) ? $price[0] : 0;
						$order_items->quantity    = $item['quantity'];
						$order_items->transaction = 0;
						$order_items->save();
					}
				}
			}
			add_option("stm_lms_set_woocommerce_order_items", "1");
		}
	}

	public static function create_table_order_items(){
		global $wpdb;
		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		$charset_collate = $wpdb->get_charset_collate();
		$table_name = $wpdb->prefix . 'stm_lms_order_items';
		$sql = "CREATE TABLE {$table_name} (
				id bigint(20) NOT NULL AUTO_INCREMENT,
				order_id bigint(20) unsigned NOT NULL,
				object_id bigint(20) unsigned NOT NULL,
				payout_id bigint(20) unsigned,
				quantity int(11) NOT NULL,
				price float(24,2),
				`transaction` varchar(100),
				PRIMARY KEY  (id),
				KEY `{$table_name}_order_id_index` (`order_id`),
				KEY `{$table_name}_object_id_index` (`object_id`),
				KEY `{$table_name}_payout_id_index` (`payout_id`)
				) {$charset_collate};";
		maybe_create_table($table_name , $sql);
	}

	/**
	 * @return StmStatistics
	 */
	public static function get_instance() {
		if ( ! isset( self::$instance ) ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * @return mixed
	 */
	public static function get_author_fee(){
		$author_fee = STM_LMS_Options::get_option('author_fee', false);
		return ($author_fee) ? $author_fee : 10;
	}

	/**
	 * @param $offset
	 * @param $limit
	 * @param array $params
	 *
	 * @return array
	 */
	public static function get_user_orders($offset, $limit, $params = []){
		global $wpdb;
		$prefix = $wpdb->prefix;
		$user_orders = [
			"items" => [],
			"total" => 0,
		];
		$query = StmOrder::query()
		                 ->select(" _order.*, meta.* ")
		                 ->asTable("_order")
		                 ->join(" left join `".$prefix."stm_lms_order_items` as lms_order_items on ( lms_order_items.`order_id` = _order.ID )
			                           left join `".$prefix."posts` as course on  (course.ID = lms_order_items.`object_id`) ")
					     ->where_in("_order.post_type", ["stm-orders", "shop_order"]);

		if(isset($params['id'])  AND !empty($params['id']) ) {
			$query->where('_order.ID', $params['id']);
		}

		if( isset($params['created_date_from']) AND !empty(trim($params['created_date_from'])) AND isset($params['created_date_to']) AND !empty(trim($params['created_date_to']))) {
			$query->where_raw('
			DATE(_order.post_date) >= "'.date( "Y-m-d", strtotime($params['created_date_from']) ).'" AND
			DATE(_order.post_date) <= "'.date( "Y-m-d", strtotime($params['created_date_to']) ).'"
		');
		}

		if(isset($params['total_price']) AND !empty($params['total_price']) ) {
			$query->where_raw(' ( meta.meta_key = "_order_total" AND meta.meta_value = "'.$params['total_price'].'" ) ');
		}

		if(isset($params['status']) AND !empty($params['status']) ) {
			$query->where_raw('
				(
					( meta.meta_key = "status" AND meta.meta_value = "'.$params['status'].'" ) OR
					( _order.post_status = "'.$params['status'].'" )
				) 
			');
		}

		if(isset($params['user']) AND !empty($params['user']) ) {
			$ids = [$params['user']];
			if(!empty($ids)){
				$query->where_raw('
				(
					(meta.meta_key = "user_id" AND meta.meta_value in ('.implode(",", $ids).')) OR
					(meta.meta_key = "_customer_user" AND meta.meta_value in ('.implode(",", $ids).'))
				)
			');
			}
		}

		if(isset($params['post_author']) AND !empty($params['post_author']) ) {
			$query->where("course.`post_author`", (int) $params['post_author']);
		}

		if ( ! empty( $params['orderby'] ) ) {
			$query->sort_by(esc_sql( $params['orderby'] ))
			      ->order(! empty( $params['order'] ) ? ' ' . esc_sql( $params['order'] ) : ' ASC');
		}else{
			$query->sort_by("ID")->order(" DESC ");
		}

		$query_total = clone $query;

		$user_orders['total'] = $query_total->select(" COUNT(DISTINCT _order.ID) as count ")->findOne()->count;
		$query->join(" left join ".$prefix."postmeta as meta on (meta.post_id = _order.ID)")
		      ->group_by("_order.ID")
		      ->limit($limit)
		      ->offset($offset);

		$user_orders['items'] = $query->find();

		return $user_orders;
	}

	/**
	 * @param $offset
	 * @param $limit
	 * @param array $params
	 *
	 * @return array
	 */
	public static function get_user_order_items($offset, $limit, $params = []){
		global $wpdb;
		$prefix = $wpdb->prefix;
		$user_orders = [
			"items" => [],
			"total" => 0,
			"total_price" => 0,
		];
		$query = StmOrderItems::query()
		                 ->select(" lms_order_items.*, course.post_title as name, _order.`post_date` as date_created ")
		                 ->asTable("lms_order_items")
		                 ->join(" left join `".$prefix."posts` as _order on ( lms_order_items.`order_id` = _order.ID )
			                           left join `".$prefix."posts` as course on  (course.ID = lms_order_items.`object_id`) ")
		                 ->where_in("_order.post_type", ["stm-orders", "shop_order"]);

		if(isset($params['id'])  AND !empty($params['id']) ) {
			$query->where('_order.ID', $params['id']);
		}

		if( isset($params['date_from']) AND !empty(trim($params['date_from'])) AND isset($params['date_to']) AND !empty(trim($params['date_to']))) {
			$query->where_raw('
			DATE(_order.post_date) >= "'.date( "Y-m-d", strtotime($params['date_from']) ).'" AND
			DATE(_order.post_date) <= "'.date( "Y-m-d", strtotime($params['date_to']) ).'"
		');
		}

		if(isset($params['total_price']) AND !empty($params['total_price']) ) {
			$query->where_raw(' ( meta.meta_key = "_order_total" AND meta.meta_value = "'.$params['total_price'].'" ) ');
		}

		if(isset($params['status']) AND !empty($params['status']) ) {
			$query->where_raw('
				(
					( meta.meta_key = "status" AND meta.meta_value = "'.$params['status'].'" ) OR
					( _order.post_status = "'.$params['status'].'" )
				) 
			');
		}

		if(isset($params['user']) AND !empty($params['user']) ) {
			$ids = [$params['user']];
			if(!empty($ids)){
				$query->where_raw('
				(
					(meta.meta_key = "user_id" AND meta.meta_value in ('.implode(",", $ids).')) OR
					(meta.meta_key = "_customer_user" AND meta.meta_value in ('.implode(",", $ids).'))
				)
			');
			}
		}

		if(isset($params['course_id']) AND !empty($params['course_id']) ) {
			$query->where("course.ID", $params['course_id']);
		}

		if(isset($params['author_id']) AND !empty($params['author_id']) AND $params['author_id'] != 0 ) {
			$query->where("course.`post_author`", (int) $params['author_id']);
		}

		if(isset($params['completed']) AND !empty($params['completed']) ) {
			$query->join(" left join ".$prefix."postmeta as meta_status on ( meta_status.post_id = _order.ID AND _order.`post_type` = 'stm-orders' AND  meta_status.`meta_key` = 'status' AND meta_status.`meta_value` = 'completed') ")
				  ->join(" left join ".$prefix."posts as order_status on ( lms_order_items.`order_id` = order_status.ID AND order_status.`post_status` = 'wc-completed') ")
			      ->where_raw(" (  meta_status.post_id = _order.ID OR order_status.ID = _order.ID )  ");
		}

		if ( ! empty( $params['orderby'] ) ) {
			$query->sort_by(esc_sql( $params['orderby'] ))
			      ->order(! empty( $params['order'] ) ? ' ' . esc_sql( $params['order'] ) : ' ASC');
		}else{
			$query->sort_by("ID")->order(" DESC ");
		}

		$query_total = clone $query;
		$user_orders['total'] = $query_total->select(" COUNT(DISTINCT lms_order_items.id) as count ")->findOne()->count;

		$query_total_price = clone $query;
		$query_total_price->select(" SUM( lms_order_items.`price` * lms_order_items.`quantity`) as total_price ");
		$total_price = $query_total_price->findOne()->total_price;
		$user_orders['total_price'] = ($total_price) ? $total_price : 0;
		$query->join(" left join ".$prefix."postmeta as meta on (meta.post_id = _order.ID)")
		      ->group_by("lms_order_items.id")
		      ->limit($limit)
		      ->offset($offset);

		$user_orders['items'] = $query->find();
		return $user_orders;
	}

	public static function get_user_orders_api(){
		$offset = 0;
		$limit = 10;

		if(isset($_GET['offset']) AND !empty($_GET['offset']))
			$offset = $_GET['offset'];

		if(isset($_GET['limit']) AND !empty($_GET['limit']))
			$limit = $_GET['limit'];

		$params = $_GET;
		$params['author_id'] = get_current_user_id();

		$params['completed'] = true;

		if($params['author_id'])
			return self::get_user_order_items($offset, $limit, $params);
	}

	/**
	 * @param $date_start
	 * @param $date_end
	 * @param $user_id
	 * @param null $course_id
	 *
	 * @return array
	 */
	public static function get_course_statisticas($date_start, $date_end, $user_id, $course_id = null){
		global $wpdb;
		$data = [];
		$courses = StmLmsCourse::query()
							  ->select(" course.ID, course.`post_title`, _order.`post_date` as date, SUM(order_items.`price` * order_items.`quantity`) as amount")
							  ->asTable("course")
						      ->join(" left join `". $wpdb->prefix ."stm_lms_order_items` as order_items on order_items.`object_id` = course.ID ")
						      ->join(" left join `". $wpdb->prefix ."posts` _order on _order.ID = order_items.`order_id` ")
						      ->join(" left join ". $wpdb->prefix ."postmeta as meta_status on ( meta_status.post_id = _order.ID AND _order.`post_type` = 'stm-orders' AND  meta_status.`meta_key` = 'status' AND meta_status.`meta_value` = 'completed') ")
							  ->where("course.post_author", $user_id)
							  ->where_raw(" ( course.post_type = 'stm-courses' OR course.post_type = 'stm-orders' ) ")
							  ->where_raw(" (_order.`post_status` = 'wc-completed' OR meta_status.post_id = _order.ID) ")
							  ->where_raw(" (DATE(_order.`post_date`) BETWEEN '".$date_start."' AND '".$date_end."') ")
							  ->group_by(" course.ID, DATE_FORMAT(_order.post_date, '%m-%Y') ");

		if($course_id != null)
			$courses->where("course.ID", $course_id)->findOne();

		foreach ($courses->find() as $course){
			$data[] = [
				"id" => $course->ID,
				"title" => $course->post_title,
				"amount" => $course->amount,
				"date" => $course->date,
				"backgroundColor" => rand_color(0.50)
			];
		}
		return $data;
	}

	/**
	 * @param $user_id
	 * @param null $course_id
	 */
	public static function get_course_sales_statisticas($user_id, $course_id = null){
		global $wpdb;
		$data = [];
		$courses = StmLmsCourse::query()
		                       ->select(" course.ID, course.`post_title`, SUM(order_items.`quantity`) as order_item_count ")
		                       ->asTable("course")
		                       ->join(" left join `". $wpdb->prefix ."stm_lms_order_items` as order_items on order_items.`object_id` = course.ID ")
		                       ->join(" left join `". $wpdb->prefix ."posts` _order on _order.ID = order_items.`order_id` ")
		                       ->join(" left join ". $wpdb->prefix ."postmeta as meta_status on ( meta_status.post_id = _order.ID AND _order.`post_type` = 'stm-orders' AND  meta_status.`meta_key` = 'status' AND meta_status.`meta_value` = 'completed') ")
		                       ->where("course.post_author", $user_id)
		                       ->where_raw(" ( course.post_type = 'stm-courses' OR course.post_type = 'stm-orders' ) ")
		                       ->where_raw(" (_order.`post_status` = 'wc-completed' OR meta_status.post_id = _order.ID) ")
		                       ->group_by(" course.ID ");

		if($course_id != null)
			$courses->where("course.ID", $course_id)->findOne();

		foreach ($courses->find() as $course){
			$data[] = [
				"id" => $course->ID,
				"title" => $course->post_title,
				"backgroundColor" => rand_color(0.50),
				"order_item_count" => $course->order_item_count
			];
		}
		return $data;
	}

}

