<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
use DB;

class UserExport implements FromArray,WithHeadings
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function array():array
	{
		$custom_array = array();


		$query = DB::table('users');
		
		$query->leftjoin('locations','locations.id','=','users.office_location');
        $query->leftjoin('centers','centers.id','=','users.centre');
        $query->leftjoin('final_scores','final_scores.user_id','=','users.id');
        $query->leftjoin('final_scores_histories','final_scores_histories.user_id','=','users.id');
		$query->select('users.id','users.name', 'users.email', 'users.phone', 'users.dob', 'users.gender','locations.name as location ','centers.name as center','users.created_at',DB::raw('(SELECT COUNT(policies.id) from policies) as total_policy'),DB::raw('COUNT(final_scores.policy_id) as completed_policy'),DB::raw('(sum(final_scores.score) /(SELECT COUNT(policies.id) from policies)) as avg_s'),DB::raw('SUM(final_scores.score) as obtained_marks'),DB::raw('(SELECT count(Q.id) as total from contents as C INNER JOIN quizzes as Q on Q.id=C.quiz_id inner join questions as Ques on Q.id=Ques.quiz_id  LEFT JOIN policies as P on P.id=C.policy_id) as totalmarks'),DB::raw('((sum(final_scores.score)*100) / (SELECT count(Q.id) as total from contents as C INNER JOIN quizzes as Q on Q.id=C.quiz_id inner join questions as Ques on Q.id=Ques.quiz_id  LEFT JOIN policies as P on P.id=C.policy_id) ) as percentage'),DB::raw('(case when (count(final_scores.policy_id) >= (SELECT COUNT(policies.id) from policies)) then "completed" else "not completed" end) as status'),'final_scores.created_at as submitted_on',DB::raw('SUM(final_scores_histories.score) as past_obtained_marks'),);
		

		$query->groupBy('users.id');
		$results  =  $query->get()->toArray() ;
		if(!empty($results))
		{
			foreach($results as $result)
			{
				// dd($result);
				$___res = array(
					'id'=>$result->id,
					'name'=>$result->name,
					'email'=>$result->email,
					'phone'=>$result->phone,
					'dob'=>$result->dob,
					'gender'=>$result->gender,
					'location'=>$result->location??'',
					'center'=>$result->center,
					'created_at'=>$result->created_at,
					'total_policy'=>$result->total_policy,
					'completed_policy'=>$result->completed_policy,
					'avg_s'=>$result->avg_s,
					'obtained_marks'=>$result->obtained_marks,
					'totalmarks'=>$result->totalmarks,
					'percentage'=>$result->percentage,
					'status'=>$result->status,
					'submitted_on'=>$result->submitted_on,
					
					
				);

				$final_scores = DB::table('final_scores_histories')->where(['user_id'=>$result->id])->groupBy(DB::raw("DATE_FORMAT(date, '%Y-%m-%d')"))->get(['created_at',DB::raw('SUM(final_scores_histories.score) as score')])->toArray();

				
				if(!empty($final_scores))
				{

					foreach($final_scores as $key=>$final_scor)
					{
						// $____attempty = [$final_scor->score,date("d-m-Y",strtotime($final_scor->created_at))];
						
						$___res['score'.$key] =$final_scor->score ;
						$___res['date'.$key] =date("d-m-Y",strtotime($final_scor->created_at));

						// array_push($___res['attempt'],$____attempty);
						
						
					}
				}


				array_push($custom_array,$___res);
			}
		}

		
		// dd($custom_array);
		return($custom_array);


		
	}

	public function headings(): array
	{
		

		return [
			'ID',
			
			'Name',
	
			'Email',
			'Phone',
			'DOB',
			'Gender',
			
            'Location',
            'Center',
			
			'Created',
			'Total_policy',
			'completed_policy',
			'Avg Score',
			'Obtained Mark',
			'totalmarks',
			'Percentage',
			'status',
			'submitted On',
			'past score 1',
			'past score date 1',
			'past score 2',
			'past score date 2',
			'past score 3',
			'past score date 3',
			'past score 4',
			'past score date 4',
			'past score 5',
			'past score date 5',

			
			
			
			
		];
	}
}