<?php

namespace App\Imports;

use App\Models\Client;
use App\Models\ClientImportAmountReport;
use App\Models\Clientsinfo;
use App\Models\Packages;
use App\Models\Pop;
use App\Models\SubPackage;
use App\Rules\NoBangla;
use App\Services\NumberValidation;
use Brian2694\Toastr\Facades\Toastr;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToCollection;
use Illuminate\Support\Facades\Validator;

class ClientsImportToCustomer implements ToCollection
{
    use Importable;

    /**
     * @param Collection $collection
     */

    public function collection(Collection $rows)
    {
        $rowNumber = 1;

        if (checkSettings('noBanglaCharacters') == 'enable') {
            $banglaRestrictedColumns = range(0, 19); // Columns to check
            for ($i = 1; $i < count($rows); $i++) {
                $row = $rows[$i];
                $rowNumber++;

                foreach ($banglaRestrictedColumns as $col) {
                    $value = trim((string) $row[$col]);

                    if (!(new NoBangla())->passes("column_$col", $value)) {
                        throw new \Exception("Bangla characters found in Row $rowNumber, Column ".$col + 1);
                    }
                }
            }
        }

        for ($i = 1; $i < count($rows); $i++) {
            $row = $rows[$i];

            $rowNumber++;
            // dd($row);
            $customRules = [
                '0' => 'required',
                '1' => 'required',
                '2' => 'required',
                '3' => 'nullable',
                '4' => 'required',
                '5' => 'nullable',
                '6' => 'required',
                '7' => 'required',
                '8' => 'nullable',
                '9' => 'nullable',
                '10' => 'nullable',
                '11' => 'nullable',
                '12' => 'nullable',
                '13' => 'nullable',
                '14' => 'nullable',
                '15' => 'nullable',
                '16' => 'nullable',
                // Add more rules as needed
            ];

            if (checkSettings('import_excel_requred') == 'enable') {
                $customRules['17'] = 'required';
                $customRules['18'] = 'required';
                $customRules['19'] = 'required';
            } else {
                $customRules['17'] = 'nullable';
                $customRules['18'] = 'nullable';
                $customRules['19'] = 'nullable';
            }

            $customAttributes = [
                '0' => 'pppoe name',
                '1' => 'pppoe password',
                '2' => 'user name',
                '3' => 'contact no',
                '4' => 'package name',
                '5' => 'reseller name',
                '6' => 'pop name',
                '7' => 'billing cycle',
                '8' => 'email',
                '9' => 'father name',
                '10' => 'mother name',
                '11' => 'area',
                '12' => 'remarks',
                '13' => 'joining date',
                '14' => 'cable type',
                '15' => 'expire date',
                '16' => 'customer code',
                '17' => 'division',
                '18' => 'district',
                '19' => 'thana',
                // Define custom attribute names for other fields as needed
            ];
            // dd($row->toArray());

            // Validate the rules against each row
            $validator = Validator::make($row->toArray(), $customRules);

            // dd($validator->errors());

            // Check for validation errors
            if ($validator->fails()) {
                // Handle validation errors, e.g., log errors or return a response
                // You can access the errors using $validator->errors()

                $errors = $validator->errors();
                $customErrors = [];
                foreach ($errors->keys() as $field) {
                    $customErrors[] = "Row $rowNumber: " . $customAttributes[$field] . ' is required <br>';
                }

                throw new \Exception(implode('', $customErrors));
            }

            $list_billing_cycle = preg_replace("/[^0-9]/", "", $row[7]);
            $int_billing_cycle = $list_billing_cycle;
            $currentMonth = Carbon::now()->month;
            $currentYear = Carbon::now()->year;

            if ($row[15] != null) {
                if (is_numeric($row[15])) {
                    // Convert the numeric value to a date using Carbon
                    $expire = Carbon::parse(($row[15] - 25569) * 86400)->format('Y-m-d 00:00:00');
                } else {
                    // If $row[15] is not numeric, use its original value
                    $expire = Carbon::parse($row[15])->format('Y-m-d 00:00:00');
                }
            } else {
                $expire = date($currentYear . '-' . $currentMonth . '-' . $int_billing_cycle . ' 00:00:00');
                $today = Carbon::parse(today())->format('Y-m-d 00:00:00');
                if (strtotime($expire) < strtotime($today)) {
                    $expire = Carbon::parse($expire)->addMonth();
                }
            }

            $pop = Pop::where('popname', $row[6])->first();
            if ($pop == null) {
                throw new \Exception($row[6] . " Pop not found" . " at row " . $rowNumber);
            }

            if ($pop->subreseller == 'yes') {
                $subPackage = SubPackage::where('name', $row[4])->first();
                if ($subPackage == null) {
                    throw new \Exception("Sub Package not found");
                }
                $package = Packages::where('id', $subPackage->package_id)->first();
            } else {
                $package = Packages::where('package_name', $row[4])->first();
                if ($package == null) {
                    throw new \Exception($row[4] . "Package not found" . " at row " . $rowNumber);
                }
            }



            if ($row[13] != null) {
                if (is_numeric($row[13])) {
                    // Convert the numeric value to a date using Carbon
                    $joiningDate = Carbon::parse(($row[13] - 25569) * 86400)->format('Y-m-d 00:00:00');
                } else {
                    // If $row[13] is not numeric, use its original value
                    $joiningDate = Carbon::parse($row[13])->format('Y-m-d 00:00:00');
                }
            } else {
                $joiningDate = '';
            }


            if (is_numeric($row[3]) && strlen($row[3]) == 10 && strlen($row[3]) < 11) {
                if (checkSettings('number_validate') == 'enable') {
                    $check = (new NumberValidation())->isBangladeshiNumber('0' . trim($row[3]));
                    if ($check) {
                        $number = '0' . trim($row[3]);
                    } else {
                        throw new \Exception($row[3] . " is not a valid Bangladeshi number" . " at row " . $rowNumber);
                    }
                } else {
                    $number = '0' . trim($row[3]);
                }
            } else {
                if (checkSettings('number_validate') == 'enable') {
                    $check = (new NumberValidation())->isBangladeshiNumber(trim($row[3]));
                    if ($check) {
                        $number = trim($row[3]);
                    } else {
                        throw new \Exception($row[3] . " is not a valid Bangladeshi number" . " at row " . $rowNumber);
                    }
                } else {
                    $number = trim($row[3]);
                }
            }



            if ($row[17] != null) {
                $division = DB::table('divisions')->where('name', $row[17])->first();
                if ($division == null) {
                    throw new \Exception($row[17] . " Division not found" . " at row " . $rowNumber);
                }
            }

            if ($row[18] != null) {
                $district = DB::table('districts')->where('name', $row[18])->first();
                if ($district == null) {
                    throw new \Exception($row[18] . " District not found" . " at row " . $rowNumber);
                }
            }
            if ($row[19] != null) {
                $upazila = DB::table('upazilas')->where('name', trim($row[19]))->first();
                if ($upazila == null) {
                    throw new \Exception($row[19] . " Thana not found" . " at row " . $rowNumber);
                }
            }

            // dd($division, $district, $upazila);

            $check_client = Client::withTrashed()->where('userid', trim($row[0]))->first();
            if ($check_client != null) {
                throw new \Exception($row[0] . " Client already exists" . " at row " . $rowNumber);
            }

            if ($check_client == null) {
                $client = new Client();
                if (is_numeric($row[0]) && strlen($row[0]) == 10 && strlen($row[0]) < 11) {
                    $client->userid = '0' . trim($row[0]);
                } else {
                    $client->userid = trim($row[0]);
                }

                if (is_numeric($row[1]) && strlen($row[1]) == 10 && strlen($row[1]) < 11) {
                    $client->password = '0' . trim($row[1]);
                } else {
                    $client->password = trim($row[1]);
                }

                $client->package_id = $package->id;
                if (isset($subPackage)) {
                    $client->sub_package_id = $subPackage->id;
                }

                $client->pop_id = $pop->id;
                $client->billing_cycle = $int_billing_cycle;
                $client->expire_date = Carbon::parse($expire)->format('Y-m-d 00:00:00');
                $client->required_cable = 0;
                $client->created_by = auth()->user()->id;
                $client->client_approval = "pending";
                $client->clients_status = "deactive";
                if (trim($row[16]) != "") {
                    $client->customer_code = trim($row[16]);
                }

                $client->save();
                $client->customer_id = $client->id;
                $client->save();

                $clientInfo = new Clientsinfo();
                $clientInfo->client_id = $client->id;
                $clientInfo->clients_name = $row[2];
                $clientInfo->contact_no = $number;
                $clientInfo->email = $row[8] ?? "";
                $clientInfo->father_name = $row[9] ?? "";
                $clientInfo->mother_name = $row[10] ?? "";
                $clientInfo->area = $row[11] ?? "";
                $clientInfo->remarks = $row[12] ?? "";
                $clientInfo->joinDate = $joiningDate;
                $clientInfo->cable_type = $row[14] ?? "";
                $clientInfo->division = $division->id ?? "";
                $clientInfo->district = $district->id ?? "";
                $clientInfo->upazila = $upazila->id ?? "";

                $clientInfo->save();

                if (checkSettings('client_import_amount_report') == 'enable') {
                    // Client Import Amount Report
                    $clientImportAmountReport = new ClientImportAmountReport();
                    $clientImportAmountReport->reseller_id = $pop->reseller_id;
                    $clientImportAmountReport->pop_id = $pop->id;
                    $clientImportAmountReport->client_id = $client->id;
                    $clientImportAmountReport->package_id = $package->id;
                    $clientImportAmountReport->package_name = $package->package_name;
                    if (isset($subPackage)) {
                        $clientImportAmountReport->sub_package_id = $subPackage->id;
                        $clientImportAmountReport->sub_package_name = $subPackage->name;
                    }

                    $dt = Carbon::now();
                    $total_days = Carbon::parse($dt)->diffInDays(Carbon::parse($expire)->format('Y-m-d 00:00:00')) + 1;

                    if ($dt > Carbon::parse($expire)->format('Y-m-d 00:00:00')) {
                        $total_days = 0;
                    }

                    if ($total_days > 0) {
                        $clientImportAmountReport->total_days = $total_days;
                        $due_amount = ($package->package_rate / 30) * $total_days;

                        $clientImportAmountReport->due_amount = $due_amount ?? 0;

                        if (isset($subPackage)) {
                            $sub_due_amount = ($subPackage->rate / 30) * $total_days;
                            $clientImportAmountReport->sub_due_amount = $sub_due_amount ?? 0;
                        }
                    } else {
                        $clientImportAmountReport->total_days = 0;
                        $clientImportAmountReport->due_amount = 0;
                        if (isset($subPackage)) {
                            $clientImportAmountReport->sub_due_amount = 0;
                        }
                    }

                    $clientImportAmountReport->package_amount = $package->package_rate;
                    if (isset($subPackage)) {
                        $clientImportAmountReport->sub_package_amount = $subPackage->rate;
                    }
                    $clientImportAmountReport->save();
                }
            }
        }
    }
}
