<?php

namespace App\Http\Controllers;

use File;
use Helper;
use App\Models\User;
use App\Models\Billing;
use App\Models\Product;
use App\Models\Mcq_history;
use App\Models\Mcq_user_answer;
use App\Imports\UsersImport;
use App\Models\Mcq;
use Illuminate\Http\Request;
use App\Models\User_device_id;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\URL;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Validator;

class ReportController extends Controller
{
	/**
	 * Display a listing of the resource.
	 *
	 * @return \Illuminate\Http\Response
	 */
	public function index(Request $request)
	{
		//dd($request->id);
		$data['mainMenu'] = 'userManagement';
		$data['subMenu'] = 'userList';
		$data['users'] = User::where(['user_unique_id' => $request->id])->first();
		$data['enrolledCourses'] = $this->enrolledCourses($request->id);
		return view('admin.report.enrolled-course-list', $data);
	}

	public function enrolledCourses($user_unique_id)
	{
		$admin_id = '2';
		$query = Billing::where(['billings.admin_id' => $admin_id])
			->join('orderproductgroups', 'billings.id', '=', 'orderproductgroups.billing_id')
			->join('users', 'users.user_unique_id', '=', 'billings.user_unique_id')
			->join('products', 'products.id', '=', 'orderproductgroups.product_id')
			->join('admin_users', 'admin_users.id', '=', 'products.teacher_id')
			->where('billings.user_unique_id', $user_unique_id)
			->orderBy('billings.id', 'DESC')->get(['billings.id as billing_id', 'billings.tax_invoice_no', 'products.id as product_id', 'products.name', 'products.image', 'products.teacher_id', 'orderproductgroups.expired', 'orderproductgroups.views', 'orderproductgroups.days', 'admin_users.username as teacher', 'billings.user_unique_id']);
		return $query;
	}

	public function resetProgressVideo(Request $request)
	{
		$validator = Validator::make($request->all(), [
			"video_id" => 'required',
			"cat_id" => 'required',
			"product_id" => 'required',
			"user_unique_id" => 'required',
			"type" => 'required'
		]);

		if ($validator->fails()) {
			return false;
		}

		if ($request->type) {
			Mcq_history::where('mcq_id', $request->video_id)->where('product_id', $request->product_id)->where('user_unique_id', $request->user_unique_id)->delete();
		} else {
			$SQL = "DELETE FROM video_histories WHERE video_histories.user_unique_id='$request->user_unique_id'
			AND video_histories.video_id='$request->video_id'
			AND video_histories.cat_id='$request->cat_id' AND video_histories.product_id='$request->product_id'";
			DB::select($SQL);
		}


		return true;
	}

	public function resetProgressCourse(Request $request)
	{
		$validator = Validator::make($request->all(), [
			"product_id" => 'required',
			"user_unique_id" => 'required'
		]);

		if ($validator->fails()) {
			return false;
		}

		$SQL = "DELETE FROM video_histories WHERE video_histories.user_unique_id='$request->user_unique_id'
        AND video_histories.product_id='$request->product_id'";
		$query = DB::select($SQL);
		return true;
	}

	public function CourseProgressReport(Request $request, $user_unique_id, $product_id)
	{
		$data['mainMenu'] = 'userManagement';
		$data['subMenu'] = 'userList';
		$data['users'] = User::where(['user_unique_id' => $request->id])->first();
		$data['courseDetails'] = $this->courseDetails($product_id);
		$data['courseContents'] = $this->courseContents($product_id, $user_unique_id);

		// Total Data
		$data['totalVideoDuration'] = $this->getTotalDurationOFProduct($product_id);
		$data['totalVideoDurationPlay'] = $this->getTotalDurationOFProductPlay($product_id, $user_unique_id);
		return view('admin.report.enrolled-course-report', $data);
	}

	public function getTotalDurationOFProductPlay($product_id, $user_unique_id)
	{
		$SQL = "SELECT SUM(video_histories.duration) as totalDuration, COUNT(video_histories.id) as totalVideos
        from contents
        JOIN products ON products.id=contents.product_id
        JOIN categories ON categories.id=contents.cat_id
        JOIN re_s3buckets ON re_s3buckets.etag=contents.video_id
        JOIN video_histories ON video_histories.video_id=contents.video_id
        WHERE contents.cat_id IN 
        (SELECT categories.id FROM categories
        WHERE categories.parent_id IN (SELECT categories.id FROM `categories`
        JOIN products ON products.id=categories.product_id
        WHERE products.id='$product_id' AND categories.parent_id=0)) 
        AND contents.type='video' 
        AND video_histories.user_unique_id='$user_unique_id'
        GROUP BY video_histories.video_id
        order by contents.cat_id,contents.type,contents.sort_order";
		$query = DB::select($SQL);
		$totalArrayPlay['totalVideosPlay'] = 0;
		$totalArrayPlay['totalVideosPlayDuration'] = 0;
		if (count($query) > 0) {
			foreach ($query as $value) {
				$totalArrayPlay['totalVideosPlay']++;
				$totalArrayPlay['totalVideosPlayDuration'] += $value->totalDuration;
			}
		}
		return $totalArrayPlay;
	}

	public function getTotalDurationOFProduct($product_id)
	{
		$SQL = "SELECT SUM(re_s3buckets.duration) as totalDuration, COUNT(re_s3buckets.public_name) as totalVideos 
        from contents
        JOIN products ON products.id=contents.product_id
        JOIN categories ON categories.id=contents.cat_id
        JOIN re_s3buckets ON re_s3buckets.etag=contents.video_id
        WHERE contents.cat_id IN 
        (SELECT categories.id FROM categories
        WHERE categories.parent_id IN (SELECT categories.id FROM `categories`
        JOIN products ON products.id=categories.product_id
        WHERE products.id='$product_id' AND categories.parent_id=0)) 
        AND contents.type='video' 
        order by contents.cat_id,contents.type,contents.sort_order";
		$query = DB::select($SQL);

		$totalArray['totalVideos'] = 0;
		$totalArray['totalVideosDuration'] = 0;
		if (count($query) > 0) {
			$totalArray['totalVideos'] = $query[0]->totalVideos;
			$totalArray['totalVideosDuration'] = $query[0]->totalDuration;
		}
		return $totalArray;
	}

	public function courseContents($product_id, $user_unique_id)
	{
		$admin_id = '2';
		$SQL = "SELECT products.name as product,products.id as product_id, categories.name as cat_name,contents.type,contents.video_id,contents.cat_id from contents
        JOIN products ON products.id=contents.product_id
        JOIN categories ON categories.id=contents.cat_id
        WHERE contents.cat_id IN 
        (SELECT categories.id FROM categories
        WHERE categories.parent_id IN (SELECT categories.id FROM `categories`
        JOIN products ON products.id=categories.product_id
        WHERE products.id=$product_id AND categories.parent_id=0))
        order by contents.cat_id,contents.type,contents.sort_order";

		$query = DB::select($SQL);
		$contentArray = array();

		if (count($query) > 0) {
			// dd($query);
			foreach ($query as $value) {
				$value->video_duration = '';
				$value->play_duration = 0;

				if ($value->type == 'video') {
					$q = DB::table('re_s3buckets')->where('etag', $value->video_id)->first();
					$value->video_duration = (!empty($q->duration)) ? $q->duration : 0;


					$sum = DB::table('video_histories')
						->where('user_unique_id', $user_unique_id)
						->where('cat_id', $value->cat_id)
						->where('product_id', $value->product_id)
						->where('video_id', $value->video_id)->sum('duration');
					$value->play_duration = $sum;
					$value->progress = "0 %";
					if ($value->play_duration > 0) {
						$value->progress = min(round(((int) $value->play_duration / 60 / ((int) $value->video_duration / 60)) * 100), 100);
					}

					// dd($value);
				} else if ($value->type == 'practice_test') {
					$q = DB::table('mcqs')->select('mcqs.name as public_name')->where('id', $value->video_id)->first();

					$value->progress = "Not Attempt";
					$queryMcq = Mcq_history::where('mcq_id', $value->video_id)->where('product_id', $product_id)->where('user_unique_id', $user_unique_id)->orderBy('id', 'ASC')->first();
					if (!empty($queryMcq)) {
						$_url = URL::to('/') . "/admin/practice-progress/" . $user_unique_id . "/" . $value->video_id;
						$value->progress = "<a class='text-success' href='" . $_url . "'><u>Result</u></a>";
					}
				} else if ($value->type == 'pdf') {
					$q = DB::table('pdf_tests')->select('pdf_tests.filename as public_name')->where('etag', $value->video_id)->first();
					$value->progress = "-";
				} else {
					$q = DB::table('ebooks_mapping')->where('etag', $value->video_id)->first();
					$value->progress = "-";
				}

				$value->item_name = (!empty($q) ? $q->public_name : '-');

				array_push($contentArray, $value);
			}
		}


		return $contentArray;
	}

	public function courseDetails($product_id)
	{
		$admin_id = '2';
		$query = Product::where(['products.id' => $product_id])->first();
		return $query;
	}

	// Practice MCQ Progress
	public function practiceProgressMCQ($user_unique_id, $mcq_id)
	{
		$data['mainMenu'] = 'userManagement';
		$data['subMenu'] = 'userList';

		// Users ==================
		$data['users'] = User::where(['user_unique_id' => $user_unique_id])->first();

		// Mcq History ============
		$mcqHistory = Mcq_history::with('quiz', 'course')->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->first();
		$data['mcqHistory'] = $mcqHistory;

		// Course =================
		$data['courseDetails'] = $mcqHistory->course()->first();

		// MCQ ====================
		$mcq = $mcqHistory->quiz()->with('mcq_question')->first();
		$data['mcq'] = $mcq;

		// MCQ Questions ==========
		$data['mcq_questions'] = $mcq->mcq_question()->with('options')->get();

		$analytic = array(
			'totalMarks' => 0,
			'yourMarks' 	=> 0,
			'correctQ' 	=> 0,
			'incorrectQ' => 0,
			'totalQ' 		=> 0,
			'skipQ' 			=> 0
		);

		// Calculate Practice Analytics =============
		foreach ($mcq->mcq_question()->with('options')->get() as $question) {
			$analytic['totalQ']++;
			$analytic['totalMarks'] = $analytic['totalMarks'] + $question->mark;
			$mark = $question->mark;
			$penalty = $question->penalty;

			// Type Fill =============
			if ($question->type == 'fill') {
				$_yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->first();
				// Not Attempt 
				if (!empty($_yourAnswer)) {
					$yourAnswer = $_yourAnswer->fill_answer;
					if (!empty($question->answer)) {
						if ($yourAnswer == $question->answer) {
							$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
							$analytic['correctQ']++;
						} else {
							$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
							$analytic['incorrectQ']++;
						}
					} else {
						if ($yourAnswer >= $question->min && $yourAnswer <= $question->max) {
							$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
							$analytic['correctQ']++;
						} else {
							$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
							$analytic['incorrectQ']++;
						}
					}
				} else {
					$analytic['skipQ']++;
				}
			}

			// Type True False ===========================
			if ($question->type == 'true_false') {
				$options = $question->options()->get();

				$_yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->first();
				// Not Attempt 
				if (!empty($_yourAnswer)) {
					foreach ($options as $option) {
						$yourAnswer = $question->user_answers()->where('option_id', $option->id);
						if ($yourAnswer->count() > 0 && $option->correct == 1) {
							$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
							$analytic['correctQ']++;
							break;
						} else {
							$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
							$analytic['incorrectQ']++;
							break;
						}
					}
				} else {
					$analytic['skipQ']++;
				}
			}

			// Type Single Choice =========================
			if ($question->type == 'single_mcq') {
				$options = $question->options()->get();
				$_yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->first();
				// Not Attempt 
				if (!empty($_yourAnswer)) {
					foreach ($options as $option) {
						$yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->where('option_id', $option->id);
						if ($yourAnswer->count() > 0 && $option->correct == 1) {
							$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
							$analytic['correctQ']++;
							break;
						} else {
							$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
							$analytic['incorrectQ']++;
							break;
						}
					}
				} else {
					$analytic['skipQ']++;
				}
			}

			// Type Multiple choice =======================
			if ($question->type == 'mcq') {
				$options = $question->options()->get();
				$totalCorrect = $question->options()->where('correct', 1)->count();
				$yourCorrect = 0;

				$_yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->first();
				// Not Attempt 
				if (!empty($_yourAnswer)) {
					foreach ($options as $option) {
						$yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->where('option_id', $option->id);
						if ($yourAnswer->count() > 0 && $option->correct == 1) {
							$yourCorrect++;
						}
					}
					if ($totalCorrect == $yourCorrect) {
						$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
						$analytic['correctQ']++;
					} else {
						$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
						$analytic['incorrectQ']++;
					}
				} else {
					$analytic['skipQ']++;
				}
			}

			// SubQuestion =========
			if ($question->group_question == '1' && $question->subquestion()->count() > 0) {
				foreach ($question->subquestion()->get() as $question) {
					$analytic['totalQ']++;
					$analytic['totalMarks'] = $analytic['totalMarks'] + $question->mark;
					$mark = $question->mark;
					$penalty = $question->penalty;

					// Type Fill =============
					if ($question->type == 'fill') {
						$_yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->first();
						// Not Attempt 
						if (!empty($_yourAnswer)) {
							$yourAnswer = $_yourAnswer->fill_answer;
							if (!empty($question->answer)) {
								if ($yourAnswer == $question->answer) {
									$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
									$analytic['correctQ']++;
								} else {
									$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
									$analytic['incorrectQ']++;
								}
							} else {
								if ($yourAnswer >= $question->min && $yourAnswer <= $question->max) {
									$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
									$analytic['correctQ']++;
								} else {
									$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
									$analytic['incorrectQ']++;
								}
							}
						} else {
							$analytic['skipQ']++;
						}
					}

					// Type True False ===========================
					if ($question->type == 'true_false') {
						$options = $question->options()->get();

						$_yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->first();
						// Not Attempt 
						if (!empty($_yourAnswer)) {
							foreach ($options as $option) {
								$yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->where('option_id', $option->id);
								if ($yourAnswer->count() > 0 && $option->correct == 1) {
									$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
									$analytic['correctQ']++;
									break;
								} else {
									$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
									$analytic['incorrectQ']++;
									break;
								}
							}
						} else {
							$analytic['skipQ']++;
						}
					}

					// Type Single Choice =========================
					if ($question->type == 'single_mcq') {
						$options = $question->options()->get();
						$_yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->first();
						// Not Attempt 
						if (!empty($_yourAnswer)) {
							foreach ($options as $option) {
								$yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->where('option_id', $option->id);
								if ($yourAnswer->count() > 0 && $option->correct == 1) {
									$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
									$analytic['correctQ']++;
									break;
								} else {
									$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
									$analytic['incorrectQ']++;
									break;
								}
							}
						} else {
							$analytic['skipQ']++;
						}
					}

					// Type Multiple choice =======================
					if ($question->type == 'mcq') {
						$options = $question->options()->get();
						$totalCorrect = $question->options()->where('correct', 1)->count();
						$yourCorrect = 0;

						$_yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->first();
						// Not Attempt 
						if (!empty($_yourAnswer)) {
							foreach ($options as $option) {
								$yourAnswer = $question->user_answers()->where('user_unique_id', $user_unique_id)->where('mcq_id', $mcq_id)->where('mcq_history_id', $mcqHistory->id)->where('option_id', $option->id);
								if ($yourAnswer->count() > 0 && $option->correct == 1) {
									$yourCorrect++;
								}
							}
							if ($totalCorrect == $yourCorrect) {
								$analytic['yourMarks'] = $analytic['yourMarks'] + $mark;
								$analytic['correctQ']++;
							} else {
								$analytic['yourMarks'] = $analytic['yourMarks'] - $penalty;
								$analytic['incorrectQ']++;
							}
						} else {
							$analytic['skipQ']++;
						}
					}
				}
			}
		}

		$data['analytic'] = $analytic;
		// dd($analytic);

		return view('admin.report.practice-mcq-report', $data);
	}
}