<?php

namespace App\Imports;

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

class ClientsImportToCustomer implements ToCollection
{
    use Importable;

    public $importErrors = [];
    public $successCount = 0;

    /**
     * @param Collection $collection
     */

    public function collection(Collection $rows)
    {
        $rowNumber = 1;
        $rowNumberBangla = 1;
        $errors = []; // Array to store all errors

        // Start database transaction
        DB::beginTransaction();

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

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

                        if (!(new NoBangla())->passes("column_$col", $value)) {
                            $errorMessage = "বাংলা অক্ষর পাওয়া গেছে  Row $rowNumberBangla, Column " . ($col + 1);
                            $errors[] = $errorMessage;
                        }
                    }
                }
            }

            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' => checkSettings('chenge_to_month') == 'enable' ? 'required' : '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';
                }

                $customRules['20'] = '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' => checkSettings('change_to_month') == 'enable' ? 'expire month' : 'expire date',
                    '16' => 'customer code',
                    '17' => 'division',
                    '18' => 'district',
                    '19' => 'thana',
                    '20' => 'building name',
                    '21' => 'expire year'
                ];
                // dd($row->toArray());

                if($row[7] > 31){
                    $errors[] = $row[7] . " Billing Cycle is not valid at row " . $rowNumber;
                    continue; // Continue to next row
                }

                if(checkSettings('change_to_month') == 'enable'){
                    if($row[15] > 12){
                        $errors[] = $row[15] . " Expire Month is not valid at row " . $rowNumber;
                        continue; // Continue to next row
                    }
                }

                // 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()

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

                    $errors = array_merge($errors, $customErrors);
                    continue; // Continue to next row instead of throwing exception
                }

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



                if(checkSettings('change_to_month') == 'enable'){
                    $expire = date($row[21] . '-' . $row[15] . '-' . $int_billing_cycle . ' 00:00:00');
                }else{
                    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();
                        }
                    }
                }

                // dd($expire);

                $pop = Pop::where('popname', $row[6])->first();
                if ($pop == null) {
                    $errors[] = $row[6] . " Pop not found at row " . $rowNumber;
                    continue; // Continue to next row
                }

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



                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 {
                            $errors[] = $row[3] . " is not a valid Bangladeshi number at row " . $rowNumber;
                            continue; // Continue to next row
                        }
                    } 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 {
                            $errors[] = $row[3] . " is not a valid Bangladeshi number at row " . $rowNumber;
                            continue; // Continue to next row
                        }
                    } else {
                        $number = trim($row[3]);
                    }
                }



                if ($row[17] != null) {
                    $division = DB::table('divisions')->where('name', $row[17])->first();
                    if ($division == null) {
                        $errors[] = $row[17] . " Division not found at row " . $rowNumber;
                        continue; // Continue to next row
                    }
                } else {
                    $division = null;
                }

                if ($row[18] != null) {
                    $district = DB::table('districts')->where('name', $row[18])->first();
                    if ($district == null) {
                        $errors[] = $row[18] . " District not found at row " . $rowNumber;
                        continue; // Continue to next row
                    }
                } else {
                    $district = null;
                }

                if ($row[19] != null) {
                    $upazila = DB::table('upazilas')->where('name', trim($row[19]))->first();
                    if ($upazila == null) {
                        $errors[] = $row[19] . " Thana not found at row " . $rowNumber;
                        continue; // Continue to next row
                    }
                } else {
                    $upazila = null;
                }

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

                $useridFromExcel = $row[0];
                $chars = str_split($useridFromExcel);

                foreach ($chars as $char) {
                    if (ord($char) == 194) {
                        $errors[] = $row[0] . " Latin character found row " . $rowNumber;
                        continue 2; // Continue to next row (skip to outer loop)
                    }
                }

                $check_client = Client::withTrashed()->where('userid', trim($row[0]))->first();
                if ($check_client != null) {
                    if (Gate::allows('update-client-excel-import') && $check_client->deleted_at != null) {
                        (new UpdateClientInfoService())->updateClientInfo($check_client, $row, $package, $subPackage, $pop, $int_billing_cycle, $expire, $joiningDate, $number, $division, $district, $upazila);
                        $this->successCount++;
                    } else {
                        $checkIfClientIsClosed = $check_client->deleted_at != null;
                        if ($checkIfClientIsClosed) {
                            $errors[] = $row[0] . " Client already exists but closed list at row " . $rowNumber;
                            continue; // Continue to next row
                        } else {
                            $errors[] = $row[0] . " Client already exists at row " . $rowNumber;
                            continue; // Continue to next row
                        }
                    }
                }


                // dd($row);
                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;
                    }

                    $billing_cycle_day = Carbon::parse($expire)->format('d');

                    $client->pop_id = $pop->id;
                    $client->billing_cycle = $billing_cycle_day; // $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->building_name = $row[20] ?? "";

                    $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();
                    }

                    $this->successCount++;
                }
            }

            // Check if there are any errors
            if (!empty($errors)) {
                // Rollback transaction if there are errors
                DB::rollback();
                $this->importErrors = $errors;
                $this->successCount = 0; // Reset success count since we rolled back
            } else {
                // Commit transaction if no errors
                DB::commit();
                $this->importErrors = [];
            }
        } catch (\Exception $e) {
            // Rollback on any unexpected exception
            DB::rollback();
            $this->importErrors = ['An unexpected error occurred: ' . $e->getMessage()];
            $this->successCount = 0;
        }
    }

    public function getErrors()
    {
        return $this->importErrors;
    }

    public function hasErrors()
    {
        return !empty($this->importErrors);
    }

    public function getSuccessCount()
    {
        return $this->successCount;
    }
}
