<?php

namespace App\Imports;

use App\Models\Academic\AcademicYear;
use App\Models\Academic\Branch;
use App\Models\Academic\Department;
use App\Models\Academic\Group;
use App\Models\Academic\Section;
use App\Models\Academic\Shift;
use App\Models\Academic\StudentClass;
use App\Models\Guardian\GuardianRelation;
use App\Models\GuardianInfo;
use App\Models\Hostel\Hostel;
use App\Models\HrPayroll\Occupation;
use App\Models\Scopes\BranchFilterScope;
use App\Models\Student\StudentInfo;
use App\Models\User;
use App\Services\StudentInfoService;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Illuminate\Support\Str;

class StudentsInfoImport implements ToCollection, WithStartRow
{
    private $importResult;
    private $duplicate = 0;
    private $imported = 0;
    protected $duplicateEntries = [];
    protected $studentInfoService;

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

    public function collection(Collection $collection)
    {
        try {
            DB::transaction(function () use ($collection) {

                $student_class_id = NULL;
                $branch_id = NULL;
                $shift_id = NULL;
                $section_id = NULL;
                $group_id = NULL;
                $department_id = NULL;
                $dormitory_id = NULL;
                $father_occupation_id = NULL;
                $mother_occupation_id = NULL;
                $guardian_occupation_id = NULL;
                $guardian_relation_id = NULL;
                $session_id = NULL;

                $student_classes = StudentClass::pluck('id', 'class_name');
                $branches = Branch::pluck('id', 'branch_name');
                $shifts = Shift::pluck('id', 'shift_name');
                $student_sections = Section::pluck('id', 'section_name');
                $groups = Group::pluck('id', 'group_name');
                $departments = Department::pluck('id', 'name');
                $hostels = Hostel::pluck('id', 'name');
                $occupations = Occupation::pluck('id', 'name');
                $guardian_relations = GuardianRelation::pluck('id', 'name');
                $sessions = AcademicYear::pluck('id', 'year');

                foreach ($collection as $row) {

                    if (!empty($row[0])) {

                        $date_of_birth = NULL;

                        if (!empty($row[6])) {
                            // Convert Excel date value to Unix timestamp (seconds since January 1, 1970)
                            $excelDateValue = intval($row[6]);

                            // Convert Excel date value to a format compatible with MySQL
                            $unixTimestamp = ($excelDateValue - 25569) * 86400;

                            // Convert Unix timestamp to a human-readable date format
                            $date_of_birth = date('Y-m-d', $unixTimestamp);
                        }

                        $mobile_number = trim($row[9]);
                        if (strlen($mobile_number) == 10) {
                            $mobile_number = '0' . $mobile_number;
                        }

                        $checkStudent = StudentInfo::where('mobile_no', $mobile_number)->where('date_of_birth', $date_of_birth)->first();

                        if (!$checkStudent) {

                            $branch_row = capitalizeWords(trim($row[15]));

                            if ($branches) {
                                $branch_id = $branches[$branch_row] ?? NULL;
                            }

                            $fullName = $row[0] . ' ' . $row[1];
                            $fullNameStr = str_replace('.', '-', $fullName);
                            $slug = Str::slug($fullNameStr);

                            $userData = [
                                'first_name' => trim($row[0] ?? ""),
                                'last_name' => trim($row[1] ?? ""),
                                'name' => $fullName,
                                'slug' => $slug,
                                'user_type' => 'student',
                                'branch_id' => $branch_id,
                                'phone_number' => $mobile_number,
                                'email' => $row[10],
                                'password' => Hash::make('12345678')
                            ];

                            $user_id = User::insertGetId($userData);

                            $gender_row = capitalizeWords(trim($row[3]));
                            $gender = $gender_row == 'Male' ? 1 : ($gender_row == 'Female' ? 2 : 3);

                            $shift_row = capitalizeWords(trim($row[13]));
                            if ($shifts) {
                                $shift_id = $shifts[$shift_row] ?? NULL;
                            }

                            $student_class_row = capitalizeWords(trim($row[14]));

                            if ($student_classes) {
                                $student_class_id = $student_classes[$student_class_row] ?? NULL;
                            }

                            $section_row = capitalizeWords(trim($row[15]));
                            if ($student_sections) {
                                $section_id = $student_sections[$section_row] ?? NULL;
                            }

                            $version_id = NULL;
                            $version_row = capitalizeWords(trim($row[16]));
                            $version_id = $version_row == 'Bangla' ? 1 : 2;

                            $group_row = capitalizeWords(trim($row[17]));
                            if ($groups) {
                                $group_id = $groups[$group_row] ?? NULL;
                            }

                            $department_row = capitalizeWords(trim($row[18]));
                            if ($departments) {
                                $department_id = $departments[$department_row] ?? NULL;
                            }

                            $session_row = capitalizeWords(trim($row[18]));
                            if ($sessions) {
                                $session_id = $sessions[$session_row] ?? NULL;
                            }

                            $hostel_row = capitalizeWords(trim($row[22]));
                            if ($hostels) {
                                $dormitory_id = $hostels[$hostel_row] ?? NULL;
                            }

                            $father_occupation_row = capitalizeWords(trim($row[26]));

                            if ($occupations) {
                                $father_occupation_id = $occupations[$father_occupation_row] ?? NULL;
                            }

                            $mother_occupation_row = capitalizeWords(trim($row[29]));

                            if ($occupations) {
                                $mother_occupation_id = $occupations[$mother_occupation_row] ?? NULL;
                            }

                            $guardian_occupation_row = capitalizeWords(trim($row[32]));

                            if ($occupations) {
                                $guardian_occupation_id = $occupations[$guardian_occupation_row] ?? NULL;
                            }

                            $guardian_relation_row = capitalizeWords(trim($row[34]));

                            if ($guardian_relations) {
                                $guardian_relation_id = $guardian_relations[$guardian_relation_row] ?? NULL;
                            }

                            $studentData = [
                                'user_id' => $user_id ?? NULL,
                                'student_id_no' => $this->studentInfoService->generateStudentId(),
                                'branch_id' => $branch_id ?? NULL,
                                'first_name' => trim($row[0]) ?? NULL,
                                'last_name' => trim($row[1]) ?? NULL,
                                'age' => trim($row[2]) ?? NULL,
                                'gender' => $gender ?? NULL,
                                'blood_group' => trim($row[4]) ?? NULL,
                                'religion' => trim($row[5]) ?? NULL,
                                'date_of_birth' => $date_of_birth,
                                'present_address' => trim($row[7]) ?? NULL,
                                'permanent_address' => trim($row[8]) ?? NULL,
                                'mobile_no' => $mobile_number ?? NULL,
                                'email' => trim($row[10] ?? NULL),
                                'birth_certi_no' => trim($row[11]) ?? NULL,
                                'shift_id' => $shift_id ?? NULL,
                                'student_class_id' => $student_class_id ?? NULL,
                                'section_id' => $section_id ?? NULL,
                                'version_id' => $version_id,
                                'group_id' => $group_id ?? NULL,
                                'department' => $department_id ?? NULL,
                                'session' => $session_id ?? NULL,
                                'admission_roll' => trim($row[20]) ?? NULL,
                                'class_roll' => !empty($row[21]) ? (int)trim($row[21]) : NULL,
                                'year_admitted' => (int)trim($row[22]) ?? NULL,
                                'dormitory_id' => $dormitory_id ?? NULL,
                                'dorm_room_no' => $row[24] ?? NULL,
                                'father_name' => trim($row[25]) ?? NULL,
                                'father_occupation' => $father_occupation_id,
                                'father_mobile' => trim($row[27]) ?? NULL,
                                'mother_name' => trim($row[28]) ?? NULL,
                                'mother_occupation' => $mother_occupation_id,
                                'mother_mobile' => trim($row[30]) ?? NULL,
                                'guardian_name' => trim($row[31]) ?? NULL,
                                'guardian_occupation' => $guardian_occupation_id,
                                'guardian_mobile' => trim($row[33]) ?? NULL,
                                'guardian_relationship' => $guardian_relation_id ?? NULL,
                                'guardian_address' => trim($row[35]) ?? NULL,
                            ];

                            $student_id = StudentInfo::create($studentData)->id;

                            if (!empty($row[31])) {
                                $guardianData = [
                                    'student_id' => $student_id,
                                    'guardian_name' => trim($row[31]) ?? NULL,
                                    'guardian_occupation' => $guardian_occupation_id,
                                    'guardian_mobile' => trim($row[33]) ?? NULL,
                                    'guardian_relationship' => $guardian_relation_id ?? NULL,
                                    'guardian_address' => trim($row[35]) ?? NULL,
                                    'guardian_email' => trim($row[36]) ?? NULL,
                                    'password' => Hash::make('12345678')
                                ];
                            } else {
                                $guardianData = [
                                    'student_id' => $student_id,
                                    'guardian_name' => trim($row[25]) ?? NULL,
                                    'guardian_occupation' => $father_occupation_id,
                                    'guardian_mobile' => trim($row[27]) ?? NULL,
                                    'guardian_relationship' => $guardian_relations['Father'] ?? NULL,
                                    'guardian_address' => trim($row[7]) ?? NULL,
                                    'guardian_email' => trim($row[36]) ?? NULL,
                                    'password' => Hash::make('12345678')
                                ];
                            }

                            GuardianInfo::create($guardianData);
                            $this->imported++;
                        } else {
                            $this->duplicate++;
                            $this->duplicateEntries[] = $row;
                        }
                    }
                }
            }, 1);
            $this->importResult = [
                'status' => true,
                'imported' => $this->imported,
                'duplicate' => $this->duplicate,
                'duplicateEntries' => $this->duplicateEntries // Pass duplicate entries to controller
            ];
        } catch (\Exception $e) {
            $this->importResult = [
                'status' => false,
                'error' => $e->getMessage(),
                'message' => 'Falied to import student data!',
            ];
        }
    }

    public function getImportResult()
    {
        return $this->importResult;
    }

    public function startRow(): int
    {
        return 2; // Skip the first row (heading row)
    }
}
