<?php

namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithDrawings;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

ini_set('gd.jpeg_ignore_warning', 1);
ini_set('memory_limit', '3048M');
class StudentExport implements FromArray, WithHeadings, WithDrawings, WithEvents, ShouldAutoSize
{

    protected $students;

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

    private function fetchStudents()
    {
        // Query
        $query = User::with('guardian', 'school', 'class', 'section');
        $query->Join('users_admissions', 'users_admissions.user_unique_id', '=', 'users.user_unique_id');
        $query->leftJoin('guardians', 'guardians.user_unique_id', '=', 'users.user_unique_id');

        // School Join
        $query->Join('users_schools', 'users_schools.user_id', '=', 'users.id');
        $query->Join('schools', 'schools.id', '=', 'users_schools.school_id');

        // Class Join
        $query->Join('users_classes_sections', 'users_classes_sections.user_id', '=', 'users.id');
        $query->Join('classes', 'classes.id', '=', 'users_classes_sections.classes_id');
        $query->Join('sections', 'sections.id', '=', 'users_classes_sections.section_id');

        // School Filter ===================
        if (isset($_GET['school']) && !empty($_GET['school'])) {
            $query->where('users_schools.school_id', $_GET['school']);
        }

        // Classes =========================
        if (isset($_GET['class']) && !empty($_GET['class'])) {
            $query->where('classes.id', $_GET['class']);
        }

        // Sections ========================
        if (isset($_GET['section']) && !empty($_GET['section'])) {
            $sections = is_array($_GET['section']) ? $_GET['section'] : [$_GET['section']];
            $query->whereIn('sections.id', $sections);
        }

        if (isset($_GET['status']) && !empty($_GET['status'])) {
            $query->where('users.status', $_GET['status']);
        }

        if (isset($_GET['gender']) && !empty($_GET['gender'])) {
            $query->where('users_admissions.gender', $_GET['gender']);
        }

        // City ==================
        if (isset($_GET['district']) && !empty($_GET['district'])) {
            $query->where('users_admissions.district', 'LIKE', "%" . $_GET['district'] . "%");
        }
        // State =================
        if (isset($_GET['state']) && !empty($_GET['state'])) {
            $query->where('users_admissions.state', $_GET['state']);
        }

        if (isset($_GET['from_date']) && !empty($_GET['from_date']) && isset($_GET['to_date']) && !empty($_GET['to_date'])) {
            $from_date = $_GET['from_date'];
            $to_date = $_GET['to_date'];
            $query->whereDate('users_admissions.created_at', '>=', $from_date);
            $query->whereDate('users_admissions.created_at', '<=', $to_date);
        }

        if (isset($_GET['type']) && !empty($_GET['type']) && isset($_GET['search']) && !empty($_GET['search'])) {
            $searchTerm = $_GET['search'];
            if ($_GET['type'] == 'first_name') {
                $query->where('users_admissions.first_name', 'LIKE', "%{$searchTerm}%");
            }
            if ($_GET['type'] == 'rollno') {
                $query->where('users_admissions.enrollment_no', 'LIKE', "%{$searchTerm}%");
            }
            if ($_GET['type'] == 'guardian_name') {
                $query->whereHas('guardian', function ($q) use ($searchTerm) {
                    $q->where('guardians.guardian_name', 'LIKE', "%{$searchTerm}%");
                    $q->orWhere('guardians.father_name', 'LIKE', "%{$searchTerm}%");
                    $q->orWhere('guardians.mother_name', 'LIKE', "%{$searchTerm}%");
                });
            }

            if ($_GET['type'] == 'parent_email') {
                $query->whereHas('guardian', function ($q) use ($searchTerm) {
                    $q->where('guardians.email', 'LIKE', "%{$searchTerm}%");
                });
            }

            if ($_GET['type'] == 'parent_phone') {
                $query->whereHas('guardian', function ($q) use ($searchTerm) {
                    $q->where('guardians.phone', 'LIKE', "%{$searchTerm}%");
                });
            }
        }

        if (isset($_GET['search']) && !empty($_GET['search'])) {
            $searchTerm = $_GET['search'];
            $query->orWhere('users.email', 'LIKE', "%{$searchTerm}%");
        }

        $query->orderBy('users_admissions.id', 'DESC');
        $students = $query->get(
            [
                'users.id as id',
                'users_admissions.id as student_id',
                'schools.school as center',
                'classes.class as attempt',
                'sections.section as batch',
                'users_admissions.previous_admission_no as old_admission_code',
                'users_admissions.enrollment_no',
                'users_admissions.date_of_admission',
                'users_admissions.inquiry_no',
                'users_admissions.for_inter_student',
                'users_admissions.for_fdn_student',
                'users.email',
                'users.phone',
                'users_admissions.first_name',
                'users_admissions.last_name',
                'users_admissions.dob',
                'users_admissions.gender',
                'users_admissions.address',
                'users_admissions.town',
                'users_admissions.pincode',
                'users_admissions.district',
                'users_admissions.state',
                'users_admissions.school_name',
                'users_admissions.ref_name',
                'users_admissions.ref_mobile',
                'users_admissions.subject_prev_class',
                'users_admissions.persuing12',
                'users_admissions.combo',
                'users_admissions.ici_no',
                'users_admissions.account_teacher_name',
                'users_admissions.account_teacher_phone',
                'users_admissions.account_teacher_school',
                'users_admissions.other_teacher_name',
                'users_admissions.other_teacher_phone',
                'users_admissions.other_teacher_school',
                'users_admissions.ca_in_family',
                'users_admissions.stream',
                'users_admissions.subject_12th',
                'users_admissions.marks_10th',
                'users_admissions.expected_12th',
                'users_admissions.other',
                'users_admissions.medium_8th',
                'users_admissions.tenth as 10th',
                'users_admissions.class_12th',
                'users_admissions.other_two',
                'users_admissions.medical_problem',
                'users_admissions.course_class',
                'users_admissions.mode',
                'users_admissions.aadhar_no',
                'users_admissions.created_at',
                'guardians.guardian_name',
                'guardians.father_name',
                'guardians.father_occupation',
                'guardians.email as father_email',
                'guardians.phone as father_phone',
                'guardians.mother_name',
                'guardians.mother_occupation',
                'guardians.mother_email',
                'guardians.mother_phone',
                'users.profile_pics',
            ]
        );
        return $students->makeHidden(['guardian', 'school', 'class', 'section']);
    }

    public function array(): array
    {
        return $this->students->toArray();
    }

    public function drawings()
    {
        $drawings = [];
        foreach ($this->students as $key => $student) {
            try {
                $drawing = new Drawing();
                $drawing->setName('Profile Picture')
                    ->setDescription('Student Profile Picture')
                    ->setPath(file_exists(public_path("/profiles/{$student->profile_pics}")) ? public_path("/profiles/{$student->profile_pics}") : public_path('/profiles/default.png'))
                    ->setWidthAndHeight(35, 35)
                    ->setCoordinates("BI" . ($key + 2));

                $drawings[] = $drawing;
            } catch (\Exception $e) {
                continue;
            }
        }
        return $drawings;
    }

    public function headings(): array
    {
        return [
            'id',
            'student_id',
            'center',
            'attempt',
            'batch',
            'old_admission_code',
            'enrollment_no',
            'date_of_admission',
            'inquiry_no',
            'for_inter_student',
            'for_fdn_student',
            'email',
            'phone',
            'first_name',
            'last_name',
            'dob',
            'gender',
            'address',
            'town',
            'pincode',
            'district',
            'state',
            'school_name',
            'ref_name',
            'ref_mobile',
            'subject_prev_class',
            'persuing12',
            'combo',
            'ici_no',
            'account_teacher_name',
            'account_teacher_phone',
            'account_teacher_school',
            'other_teacher_name',
            'other_teacher_phone',
            'other_teacher_school',
            'ca_in_family',
            'stream',
            'subject_12th',
            'marks_10th',
            'expected_12th',
            'other',
            'medium_8th',
            '10th',
            'class_12th',
            'other_two',
            'medical_problem',
            'course_class',
            'mode',
            'aadhar_no',
            'created_at',
            'guardian_name',
            'father_name',
            'father_occupation',
            'father_email',
            'father_phone',
            'mother_name',
            'mother_occupation',
            'mother_email',
            'mother_phone',
            'profile_pics'
        ];
    }

    public function registerEvents(): array
    {
        return [AfterSheet::class => fn(AfterSheet $event) => array_map(fn($key) => $event->sheet->getDelegate()->getRowDimension($key + 1)->setRowHeight(40), array_keys($this->students->toArray()))];
    }
}