<?php

namespace App\Exports;

use App\Helpers\Frontend;
use App\Models\User;
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
    {
        // dd($this->request->all());
        // $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)) {
            $query->where('testresults.class_code', $this->request->class);
            $subject->where('testresults.class_code', $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);
                });
            }

            if (!empty($this->request->section)) {
                $subject->whereHas('section', function ($q) {
                    $q->where('sections.id', $this->request->section);
                });
            }

            // Test Code ===========
            if (!empty($this->request->test_code)) {
                $subject->where('testresults.testcode', $this->request->test_code);
                $query->where('testresults.testcode', $this->request->test_code);
            }

            // 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]);
            }

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

            $subject->groupBy('testresults.testcode');
            $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;
        }
    }
}