<?php

namespace App\Exports;

use App\Models\User;
use App\Helpers\Frontend;
use App\Models\Testresult;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromArray;

// use Maatwebsite\Excel\Concerns\WithHeadings;
// use Maatwebsite\Excel\Concerns\FromCollection;

class TestresultExport implements FromArray
{
 use Exportable;
 protected $request;

 public function __construct($request)
 {
  $this->request = $request;
 }

 public function array(): array
 {
  // $schoolCodes = Frontend::get_schools();

  // Students
  $data['subjects'] = [];
  $data['results'] = [];

  // subject
  $subject = User::with('school', 'class', 'section', 'attendance');
  $subject->Join('users_admissions', 'users.user_unique_id', '=', 'users_admissions.user_unique_id');
  $subject->Join('testresults', 'testresults.users_admission_id', '=', 'users_admissions.id');

  // result
  $query = User::with('school', 'class', 'section', 'attendance');
  $query->Join('guardians_users', 'guardians_users.user_id', '=', 'users.id');
  $query->Join('guardians', 'guardians.id', '=', 'guardians_users.guardian_id');
  $query->Join('users_admissions', 'users.user_unique_id', '=', 'users_admissions.user_unique_id');
  $query->Join('testresults', 'testresults.users_admission_id', '=', 'users_admissions.id');

  if (!empty($this->request->roll_no)) {
   $query->where('users_admissions.enrollment_no', 'like', '%' . $this->request->roll_no . '%');
   $subject->where('users_admissions.enrollment_no', 'like', '%' . $this->request->roll_no . '%');
  }

  if (!empty($this->request->class)) {

   // School filter
   if (!empty($this->request->school)) {
    $query->whereHas('school', function ($q) {
     $q->where('schools.id', $this->request->school);
    });
   }

   // Class filter
   $query->whereHas('class', function ($q) {
    $q->where('classes.id', $this->request->class);
   });

   // Subject School filter
   if (!empty($this->request->school)) {
    $subject->whereHas('school', function ($q) {
     $q->where('schools.id', $this->request->school);
    });
   }

   // Subject Class filter
   $subject->whereHas('class', function ($q) {
    $q->where('classes.id', $this->request->class);
   });

   // Optional Section Filter ===================
   if (!empty($this->request->section)) {
    // dd($request->section);
    $query->whereHas('section', function ($q) {
     $q->where('sections.id', $this->request->section);
    });
   }

   // Date optional =======================
   if (!empty($this->request->date)) {
    $date = explode(',', $this->request->date);
    $from = date("Y-m-d", strtotime(trim($date[0])));
    $to = date("Y-m-d", strtotime(trim($date[1])));
    $query->whereBetween('testresults.date', [$from, $to]);
   }

   $subject->groupBy('testresults.testcode');

   if (!empty($this->request->date)) {
    $subject->whereBetween('testresults.date', [$from, $to]);
   }

   $subjects = $subject->get(['testresults.testcode', 'testresults.max_marks', 'users_admissions.enrollment_no', DB::raw('date(testresults.date) as date'), 'testresults.created_at'])->toArray();

   $query->groupBy('testresults.users_admission_id');
   $results = $query->get(['users.id as user_id', 'users.name', 'users.user_unique_id', 'users_admissions.id as admissionId', 'users_admissions.enrollment_no', 'users.phone', 'users_admissions.previous_admission_no', 'guardians.guardian_name', 'testresults.testcode'])->toArray();

   $clas = Frontend::getClass($this->request->class ?? '');
   $section = Frontend::getSection($this->request->section ?? '');

   $title = $clas->class;
   if (!empty($section)) {
    $title .= " - " . $section->section;
   }

   $row = array();

   $_date[] = "-";
   $_date[] = $title;
   $_date[] = "-";
   $_date[] = "-";
   $_date[] = "-";
   $_date[] = "-";
   $_date[] = "DATE";

   $head[] = "-";
   $head[] = "-";
   $head[] = "-";
   $head[] = "-";
   $head[] = "-";
   $head[] = "-";
   $head[] = "TEST CODE";
   $max_marks[] = "-";
   $max_marks[] = "-";
   $max_marks[] = "-";
   $max_marks[] = "-";
   $max_marks[] = "-";
   $max_marks[] = "-";
   $max_marks[] = "MARKS";
   $head1[] = 'SNO';
   $head1[] = 'OLD ADMISSION CODE';
   $head1[] = 'BATCH';
   $head1[] = 'CODE';
   $head1[] = 'NAME';
   $head1[] = 'MOBILE';
   $head1[] = 'FATHER NAME';
   foreach ($subjects as $subject) {
    $_date[] = $subject['date'];
    $head[] = $subject['testcode'];
    $max_marks[] = $subject['max_marks'];
    $head1[] = 'MARKS';
   }
   $head1[] = 'TOTAL TEST';
   $head1[] = 'TOTAL ABSENT';
   $head1[] = 'TOTAL PRESENT';
   $head1[] = '(%) OF PRESENT';
   $head1[] = 'TOTAL OBTAIN';
   $head1[] = 'TOTAL MARKS';
   $head1[] = '(%)AGE';

   array_push($row, $_date);
   array_push($row, $head);
   array_push($row, $max_marks);
   array_push($row, $head1);

   $resultArr[] = array();
   $i = 1;
   foreach ($results as $result) {
    $resultData = array();
    $resultData[] = $i++;
    $resultData[] = $result['previous_admission_no'];
    $resultData[] = $result['enrollment_no'];
    $resultData[] = $result['admissionId'];
    $resultData[] = $result['name'];
    $resultData[] = $result['phone'];
    $resultData[] = $result['guardian_name'];
    $totalObtain = 0;
    foreach ($subjects ?? '' as $subject) {

     $marks = Frontend::getUserMarks($result['admissionId'], $subject['testcode']);
     $resultData[] = empty($marks) ? '0' : $marks;
     $totalObtain = $totalObtain + (int) $marks ?? '';
    }

    $mark = 0;
    foreach ($subjects ?? '' as $subject) {
     $marks = Frontend::getTotalTest($result['admissionId'], $subject['testcode']);
     if ($marks !== '---') {
      $mark++;
     }
    }
    // Total test
    $resultData[] = $mark;

    $absent = 0;
    foreach ($subjects ?? '' as $subject) {
     $___absent = Frontend::getUserAbsent($result['admissionId'], $subject['testcode']);

     $absent += $___absent;
    }

    // Total absent
    $resultData[] = $absent ?? '';
    $present = $mark - $absent;
    // Total Present
    $resultData[] = $present ?? '';


    if ($mark > 0) {
     $resultData[] = round(($present * 100) / $mark, 2);
    } else {
     $resultData[] = 0;
    }
    // total obtained
    $resultData[] = $totalObtain;
    $_max_marks = 0;

    foreach ($subjects ?? '' as $subject) {
     $max_marks = Frontend::getUserTotalMarks($result['admissionId'], $subject['testcode']);
     if (intval($max_marks) >= 0) {
      $_max_marks += intval($max_marks);
     }
    }

    // Total max Marks
    $resultData[] = $_max_marks;

    if ($_max_marks > 0) {
     $resultData[] = round(($totalObtain * 100) / $_max_marks, 2);
    } else {
     $resultData[] = 0;
    }
    $resultArr[] = $resultData;
    // SNO
   }
   array_push($row, $resultArr);
   return $row;
  }
 }
}