<?php

namespace App\Exports;

use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Illuminate\Support\Facades\Session;
use App\Models\{
    User
};
use Maatwebsite\Excel\Concerns\FromArray;

class ParentExport implements FromArray, WithHeadings
{

    public function array(): array
    {
        // Query
        $query = User::with('guardian', 'school', 'class', 'section');
        $query->Join('users_admissions', 'users_admissions.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'])) {
            $query->where('sections.id', $_GET['section']);
        }


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

        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',
                'classes.class as class',
                'sections.section as section',
                'users_admissions.previous_admission_no as old_admission_code',
                'users_admissions.enrollment_no',
                'users.email',
                'users.phone',
                'users_admissions.first_name',
                'users_admissions.last_name',
                'users_admissions.dob',
                'users_admissions.gender'
            ]
        );

        $collection = array();
        foreach ($students as $student) {
            $student->father_name = "";
            $student->father_email = "";
            $student->father_phone = "";
            $student->father_occupation = "";
            $student->mother_name = "";
            $student->mother_email = "";
            $student->mother_phone = "";
            $student->mother_occupation = "";
            $student->parent_address = "";
            $student->parent_town = "";
            $student->parent_district = "";
            $student->parent_pincode = "";
            $student->parent_state = "";

            if ($student->guardian->first()) {
                $student->father_name = $student->guardian->first()->father_name;
                $student->father_email = $student->guardian->first()->email;
                $student->father_phone = $student->guardian->first()->phone;
                $student->father_occupation = $student->guardian->first()->father_occupation;
                $student->mother_name = $student->guardian->first()->mother_name;
                $student->mother_email = $student->guardian->first()->mother_email;
                $student->mother_phone = $student->guardian->first()->mother_phone;
                $student->mother_occupation = $student->guardian->first()->mother_occupation;
                $student->parent_address = $student->guardian->first()->address;
                $student->parent_town = $student->guardian->first()->town;
                $student->parent_district = $student->guardian->first()->district;
                $student->parent_pincode = $student->guardian->first()->pincode;
                $student->parent_state = $student->guardian->first()->state;
            }
            array_push($collection, $student);
        }

        return $collection;

    }

    public function headings(): array
    {
        return [
            'id',
            'class',
            'section',
            'old_admission_code',
            'enrollment_no',
            'email',
            'phone',
            'first_name',
            'last_name',
            'dob',
            'gender',
            'father_name',
            'father_email',
            'father_phone',
            'father_occupation',
            'mother_name',
            'mother_email',
            'mother_phone',
            'mother_occupation',
            'parent_address',
            'parent_town',
            'parent_district',
            'parent_pincode',
            'parent_state',
        ];
    }
}