<?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
};

class StudentExport implements FromCollection, WithHeadings
{
	/**
	 * @return \Illuminate\Support\Collection
	 */
	public function collection()
	{
		$query = User::with('guardian', 'school', 'class', 'section');
		
		$query->Join('users_admissions', 'users_admissions.user_unique_id', '=', 'users.user_unique_id');
		// 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');

		return $query->get(
			[
				'users_admissions.id', 
				'classes.class as class',
				'sections.section as section',
				'users_admissions.previous_admission_no as old_admission_code',				
				'users_admissions.enrollment_no', 
				'users_admissions.date_of_admission',
				'users_admissions.inquiry_no', 
				'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.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.aadhar_no',
				'users_admissions.created_at']);

	}

	public function headings(): array
	{
		return [
			'id',
			'class',
			'section',
			'old_admission_code',
			'enrollment_no',
			'date_of_admission',
			'inquiry_no',
			'email',
			'phone',
			'first_name',
			'last_name',
			'dob',
			'gender',
			'address',
			'town',
			'pincode',
			'district',
			'state',
			'school_name',
			'ref_name',
			'subject_prev_class',
			'persuing12',
			'combo',
			'ici_no',
			'account_teacher_name',
			'account_teacher_phone',
			'aadhar_no',
			'created_at'
		];
	}
}