<?php


namespace App\Imports;


use App\Models\Employee;

use Maatwebsite\Excel\Concerns\ToCollection;

use Maatwebsite\Excel\Concerns\WithStartRow;

use Illuminate\Support\Collection;

use Carbon\Carbon;  // Import Carbon for date handling


class EmployeeImport implements ToCollection, WithStartRow

{

    public function collection(Collection $rows)

    {

        foreach ($rows as $row) {

            // Check if 'basic_pay' is missing and set it to a default value

            $basicPay = $row[6] ?: 0;  // Default to 0 if empty or invalid


            // Validate 'date_of_appt' (column 13) and set default if invalid

            $dateOfAppt = $this->validateDate($row[13]) ?: Carbon::now()->toDateString(); // Default to current date if invalid


            // Ensure 'house_rent' is numeric, default to 0 if not

            $houseRent = is_numeric($row[17]) ? $row[17] : 0; // Set default to 0 if not numeric


            Employee::create([

                // 'emp_no'          => $row[0],

                // 'emp_name'        => $row[1],

                // 'f_h_name'        => $row[2],

                // 'cnic'            => $row[3],

                // 'area'            => $row[4],

                // 'basic_pay_scale' => $row[5],

                // 'basic_pay'       => $basicPay,  // Use the default value if missing

                // 'designation'     => $row[7],

                // 'department'      => $row[8],

                // 'section'         => $row[9],

                // 'bank_account'    => $row[10],

                // 'iban'            => $row[11],

                // 'bank_name'       => $row[12],

                // 'date_of_appt'    => $dateOfAppt,  // Use the validated date

                // 'date_of_ret'     => $row[14],

                // 'religion'        => $row[15],

                // 'gender'          => $row[16],

                // 'house_rent'      => $houseRent,  // Use the numeric value or default to 0

                // 'convence'        => $row[18],

                // 'medical'         => $row[19],

                // 'dr'              => $row[20],

                // 'adhoc_2022'      => $row[21],

                // 'adhoc_2023'      => $row[22],

                // 'adhoc_2024'      => $row[23],

                // 'differential'    => $row[24],

                // 'washing_effici'  => $row[25],

                // 'pen_cont'        => $row[26],

                // 'income_tax'      => $row[27],

                // 'group_ins'       => $row[28],

                // 'prov_fund'       => $row[29],

                // 'welfare_fund'    => $row[30],
                'emp_no' => $row[0],
                'emp_name' => $row[1],
                'f_h_name' => $row[2],
                'cnic' => $row[3],
                'area' => $row[4],
                'basic_pay_scale' => $row[5],
                'dob' => $row[6],
                'basic_pay' => $basicPay,
                'designation' => $row[8],
                'billno' => $row[9],
                'department' => $row[10],
                'section' => $row[11],
                'bank_account' => $row[12],
                'iban' => $row[13],
                'bank_name' => $row[14],
                'date_of_appt' => $dateOfAppt,
                'date_of_ret' => $row[16],
                'religion' => $row[17],
                'gender' => $row[18],
                'status' => $row[19],
                'house_rent' => $houseRent,
                'pension' => $row[21],
                'convence' => $row[22],
                'medical' => $row[23],
                'dr' => $row[24],
                'adhoc_2022' => $row[25],
                'adhoc_2023' => $row[26],
                'adhoc_2024' => $row[27],
                'differential' => $row[28],
                'washing_effici' => $row[29],
                'pen_cont' => $row[30],
                'income_tax' => $row[31],
                'group_ins' => $row[32],
                'prov_fund' => $row[33],
                'welfare_fund' => $row[34],
                'loan' => $row[35],
            ]);

        }

    }


    public function startRow(): int

    {

        return 2; // Assuming the first row is a header row

    }


    // Function to validate date

    private function validateDate($date)

    {

        // Check if the date is valid using Carbon

        try {

            return Carbon::parse($date)->toDateString();

        } catch (\Exception $e) {

            return false;  // Return false if the date is invalid

        }

    }

}