<?php

namespace App\Http\Controllers;

use App\Classes\EditLogHistory;
use App\Classes\SMS\TokenSms;
use App\Classes\TokenClass;
use App\Jobs\SendSmsJob;
use App\Models\Balance;
use App\Models\Client;
use App\Models\Clientsinfo;
use App\Models\ClientSource;
use App\Models\CustomerAccount;
use App\Models\Departmant;
use App\Models\LogHistory;
use App\Models\Employee;
use App\Models\Income;
use App\Models\IncomeHead;
use App\Models\NewLineRequest;
use App\Models\NewLineRequestMarketinCancelReason;
use App\Models\NewLineRequestRemarks;
use App\Models\Packages;
use App\Models\Pop;
use App\Models\SubPackage;
use App\Models\Thana;
use App\Models\Upazila;
use App\Services\ExpireCheck;
use App\Services\PriceCalculate;
use App\Http\Controllers\Customer\TokenController;
use App\Models\Token;
use App\Models\User;
use Carbon\Carbon;
use Carbon\PHPStan\MacroScanner;
use Exception;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Http\Request;
use Brian2694\Toastr\Facades\Toastr;

class NewLineController extends Controller
{
    public function getDivision()
    {
        return divisions();
    }

    public function getDistrict($id)
    {
        return districts()->where('division_id', $id);
    }

    public function getUpazila($id)
    {
        return upazilas()->where('district_id', $id);
    }

    public function getThana($id)
    {
        return thanas()->where('upazila_id', $id);
    }

    public function getSource()
    {
        return ClientSource::where('status', 'yes')->orderBy('order', 'asc')->orderBy('name', 'asc')->get();
    }

    public function newLineAdd(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'name' => 'required',
            'phone' => 'required|digits:11,phone',
            'otherContact' => 'digits:11,otherContact',
            'address' => 'required',
            'upazila' => 'required',
            'district' => 'required',
            'division' => 'required',
            'source' => 'required',
            'thana' => 'required',

        ]);

        if ($validator->fails()) {
            return response()->json([
                'error' => true,
                'message' => $validator->errors()
            ]);
        }

        if (NewLineRequest::where('phone', $request->phone)->whereDate('created_at', today())->count() > 0) {


            return response()->json([
                'error' => true,
                'message' => ['already_exist' => 'Your are already applied for new line. Please try after 24 hour.']
            ]);
        }

        // dd($request->all());

        $newLine = new NewLineRequest();

        $newLine->name = $request->input('name');
        $newLine->email = $request->input('email');
        $newLine->phone = $request->input('phone');
        // $newLine->otherContact = $request->input('otherContact');
        $newLine->address = $request->input('address');
        $newLine->upazila_id = $request->input('upazila');
        $newLine->district_id = $request->input('district');
        $newLine->division_id = $request->input('division');
        $newLine->thana_id = $request->input('thana');

        $newLine->line_status = 'new_request';
        $newLine->source_id = $request->input('source');

        $newLine->ip = $request->ip();
        $newLine->customer_status = "Survey";

        $newLine->save();

        $checkSurveyStatus = false;

        //check coverage status
        $than = Thana::find($request->input('thana'));
        if ($than->coverage_status == 'Not Coverage') {
            $newLine->survey_status = 'Not Coverage';
            $newLine->customer_status = 'Marketing';
            $newLine->save();
            $checkSurveyStatus = true;
        }


        if ($checkSurveyStatus) {
            $not_coverage_message = siteinfo()->not_coverage_message ?? "This area is not covered by the network. We will inform you soon about the coverage status.";


            $data = [
                'contact_no' => $newLine->phone,
                'message' => $not_coverage_message,
                'type' => "New Line Request",
            ];
            try {
                SendSmsJob::dispatch($data);
            } catch (\Exception $e) {
            }
            return response()->json([
                'error' => false,
                'message' => $not_coverage_message,
            ]);
        } else {
            $new_line_save_message = siteinfo()->new_line_save_message ?? "Data saved successfully";
            return response()->json([
                'error' => false,
                'message' => $new_line_save_message,
            ]);
        }
    }

    public function newLineRequestRemarks(Request $request)
    {

        $newLineRequestRemarks = NewLineRequestRemarks::with('user')
            ->where('new_line_request_id', $request->id)
            ->orderBy('created_at', 'desc')
            ->get()
            ->map(function ($remark) {
                return [
                    'id'         => $remark->id,
                    'remarks'    => $remark->remarks,
                    'created_at' => optional($remark->created_at)->format('Y-m-d H:i:s'),
                    'user'       => [
                        'id'   => optional($remark->user)->id,
                        'name' => optional($remark->user)->name,
                    ],
                ];
            });

        return response()->json([
            'error'   => false,
            'message' => 'Remarks fetched successfully',
            'data'    => $newLineRequestRemarks,
        ]);
    }


    public function remarks($id)
    {
        $newLineRequest = NewLineRequest::findOrFail($id);

        // Check if it's an AJAX request for JSON
        if (request()->wantsJson() || request()->is('*/json')) {
            $remarks = $newLineRequest->remarksList()
                ->with('user')
                ->orderBy('created_at', 'desc')
                ->get()
                ->map(function ($remark) {
                    return [
                        'id' => $remark->id,
                        'remarks' => $remark->remarks,
                        'user_name' => $remark->user->name ?? 'Unknown User',
                        'created_at' => $remark->created_at->format('Y-m-d h:i A'),
                    ];
                });

            return response()->json([
                'success' => true,
                'remarks' => $remarks
            ]);
        }

        // Regular page view (keep this for backward compatibility if needed)
        $remarks = $newLineRequest->remarksList()->orderBy('created_at', 'desc')->get();
        return view('clients.newclient.remarks', compact('remarks', 'newLineRequest'));
    }

    public function storeRemark(Request $request, $id)
    {
        $request->validate([
            'remarks' => 'required|string'
        ]);

        $newLineRequest = NewLineRequest::findOrFail($id);

        $remark = $newLineRequest->remarksList()->create([
            'remarks' => $request->remarks,
            'user_id' => auth()->id()
        ]);

        // Return JSON response for AJAX
        if ($request->wantsJson()) {
            $remark->load('user');
            return response()->json([
                'success' => true,
                'message' => 'Remark added successfully!',
                'remark' => [
                    'id' => $remark->id,
                    'remarks' => $remark->remarks,
                    'user_name' => $remark->user->name ?? 'You',
                    'created_at' => $remark->created_at->format('Y-m-d h:i A'),
                ]
            ]);
        }

        return redirect()->back()->with('success', 'Remark added successfully!');
    }

    public function newLineRequestRemarksAdd(Request $request)
    {
        $newLineRequestRemarks = new NewLineRequestRemarks();
        $newLineRequestRemarks->new_line_request_id = $request->id;
        $newLineRequestRemarks->remarks = $request->remarks;
        $newLineRequestRemarks->created_by = auth()->id();

        $newLineRequestRemarks->save();

        $newLineRequestRemarks = NewLineRequestRemarks::with('user')
            ->where('new_line_request_id', $request->id)
            ->orderBy('created_at', 'desc')
            ->get()
            ->map(function ($remark) {
                return [
                    'id'         => $remark->id,
                    'remarks'    => $remark->remarks,
                    'created_at' => optional($remark->created_at)->format('Y-m-d H:i:s'),
                    'user'       => [
                        'id'   => optional($remark->user)->id,
                        'name' => optional($remark->user)->name,
                    ],
                ];
            });

        return response()->json([
            'error'   => false,
            'message' => 'Remark added successfully',
            'data'    => $newLineRequestRemarks,
        ]);
    }

    public function changeMarketingStatus(Request $request)
    {
        DB::beginTransaction();
        try {


            $old_value = NewLineRequest::with('cancelReasons')->select('delivery_date', 'confirm_activation_date', 'marketing_user_id', 'marketing_done', 'id', 'marketing_done_by', 'approval_remarks', 'follow_up_date')->find($request->id);


            $newLineRequest = NewLineRequest::find($request->id);
            if ($request->marketingStatus == 1) {
                //check confirm line date
                if ($newLineRequest->confirm_activation_date == null) {
                    return response()->json([
                        'error' => true,
                        'message' => 'Before the shift to confirm the line, please update the activation date.',
                    ]);
                }
            }

            if ($request->marketingStatus == 2) {
                //check confirm line date
                if ($request->followUpDate == null) {
                    return response()->json([
                        'error' => true,
                        'message' => 'Please select a follow-up date to shift the follow-up.',
                    ]);
                }
            }
            $newLineRequest->follow_up_date = $request->followUpDate;
            $newLineRequest->marketing_done = $request->marketingStatus;
            $newLineRequest->marketing_done_by = auth()->user()->id;
            $newLineRequest->approval_remarks = $request->marketingRemarks;
            $newLineRequest->confirm_activation_date = $request->confirmation;
            $newLineRequest->marketing_user_id = $request->marketingUserId;

            if ($request->marketingStatus == 1) {
                $newLineRequest->line_status = 'confirm_request';
                $newLineRequest->delivery_date = $request->confirmation;
            }
            $newLineRequest->save();

            if (isset($request->cancelReasons)) {

                NewLineRequestMarketinCancelReason::where('new_line_request_id', $request->id)->delete();
                foreach ($request->cancelReasons as $cancelReason) {
                    NewLineRequestMarketinCancelReason::create([
                        'new_line_request_id' => $request->id,
                        'cancel_reason_id' => $cancelReason,
                    ]);
                }
            }


            $new_value = NewLineRequest::with('cancelReasons')->select('delivery_date', 'confirm_activation_date', 'marketing_user_id', 'marketing_done', 'id', 'marketing_done_by', 'approval_remarks', 'follow_up_date')->find($request->id);

            $type = 'new_line_request_update';

            // dd($old_data,$new_info);
            (new EditLogHistory)->editLogSave($newLineRequest, $type, $old_value, $new_value);

            DB::commit();
            return response()->json([
                'error' => false,
                'message' => 'Marketing status updated successfully',
            ]);
        } catch (Exception $e) {
            DB::rollBack();
            return response()->json([
                'error' => true,
                'message' => 'Marketing status update failed ' . $e->getMessage(),
            ]);
        }
    }

    public function newLineRequestHistoryLogs($id)
    {
        $edit_logs = LogHistory::where('log_id', $id)->where('log_type', 'new_line_request_update')->orderBy('id', 'desc')->get();

        $data = [
            'lists' => $edit_logs,
            'type' => 'New Line Request',
        ];
        return view('clients.newclient.historyLogs', $data);
    }

    public function assignRequest(Request $request)
    {
        DB::beginTransaction(); // Fixed: was beforeExecuting()
        try {
            $request->validate([
                'id' => 'required|exists:new_line_requests,id',
                'user_id' => 'required|exists:users,id',
                'assign_remarks' => 'nullable|string|max:1000'
            ]);

            $old_value = NewLineRequest::select('assigned_to', 'id', 'assigned_by')->find($request->id);
            $newLineRequest = NewLineRequest::find($request->id);

            // Update assignment
            $newLineRequest->assigned_to = $request->user_id;
            $newLineRequest->assigned_by = auth()->id();
            $newLineRequest->save();

            // Add remark if provided
            if ($request->assign_remarks) {
                $newLineRequestRemark = new NewLineRequestRemarks();
                $newLineRequestRemark->new_line_request_id = $request->id;
                $newLineRequestRemark->remarks = $request->assign_remarks;
                $newLineRequestRemark->created_by = auth()->user()->id;
                $newLineRequestRemark->save();
            }

            $new_value = NewLineRequest::select('assigned_to', 'id', 'assigned_by')->find($request->id);

            // Log the change
            (new EditLogHistory)->editLogSave($newLineRequest, 'new_line_request_update', $old_value, $new_value);

            DB::commit();

            return response()->json([
                'error' => false,
                'message' => 'Request assigned successfully!'
            ]);
        } catch (Exception $e) {
            DB::rollBack();
            return response()->json([
                'error' => true,
                'message' => 'Assignment failed: ' . $e->getMessage(),
            ], 500); // Added HTTP status code
        }
    }

    public function setDeliveryDate(Request $request)
    {
        DB::beginTransaction(); // Fixed: was beforeExecuting()
        try {
            $request->validate([
                'id' => 'required|exists:new_line_requests,id',
                'delivery_date' => 'required|date',
            ]);
            $old_value = NewLineRequest::select('delivery_date', 'id')->find($request->id);


            $newLineRequest = NewLineRequest::find($request->id);
            $newLineRequest->delivery_date = $request->delivery_date;
            $newLineRequest->save();

            $new_data = NewLineRequest::select('delivery_date', 'id')->find($request->id);
            (new EditLogHistory)->editLogSave($newLineRequest, 'new_line_request_update', $old_value, $new_data);

            DB::commit();
            $formattedDate = \Carbon\Carbon::parse($newLineRequest->delivery_date)->format('Y-m-d');

            return response()->json([
                'error' => false,
                'message' => 'Delivery date set successfully!',
                'delivery_date' => $formattedDate,
            ]);
        } catch (Exception $e) {
            DB::rollBack();
            return response()->json([
                'error' => true,
                'message' => 'Delivery date set failed: ' . $e->getMessage(),
            ], 500); // Added HTTP status code
        }
    }

    public function newLineRequestSetFreeDays(Request $request)
    {
        DB::beginTransaction(); // Fixed: was beforeExecuting()
        try {

            $request->validate([
                'id' => 'required|exists:new_line_requests,id',
                'free_days' => 'required|integer|min:0',
            ]);

            $old_value = NewLineRequest::select('free_days', 'id')->find($request->id);
            $newLineRequest = NewLineRequest::find($request->id);
            $newLineRequest->free_days = $request->free_days;
            $newLineRequest->save();

            $new_data = NewLineRequest::select('free_days', 'id')->find($request->id);
            (new EditLogHistory)->editLogSave($newLineRequest, 'new_line_request_update', $old_value, $new_data);

            DB::commit();
            return response()->json([
                'error' => false,
                'message' => 'Free days set successfully!',
                'free_days' => $newLineRequest->free_days,
            ]);
        } catch (Exception $e) {
            DB::rollBack();
            return response()->json([
                'error' => true,
                'message' => 'Free days set failed: ' . $e->getMessage(),
            ], 500);
        }
    }

    public function assignLineMan(Request $request)
    {
        DB::beginTransaction();
        try {
            $request->validate([
                'id' => 'required|exists:new_line_requests,id',
                'line_manager_id' => 'required|exists:employees,id',
            ]);

            $old_value = NewLineRequest::select('line_manager_id', 'id')->find($request->id);
            $newLineRequest = NewLineRequest::find($request->id);
            $newLineRequest->line_manager_id = $request->line_manager_id;
            $newLineRequest->save();

            $new_data = NewLineRequest::select('line_manager_id', 'id')->find($request->id);

            (new EditLogHistory)->editLogSave($newLineRequest, 'new_line_request_update', $old_value, $new_data);


            // create client
            $client = $this->createPppoeUserFromConfirmLineRequest($newLineRequest->id);

            if ($client['success'] == 'Faild') {

                DB::rollBack();
                return response()->json([
                    'error' => true,
                    'message' => $client['message'],
                ], 500);
            }

            // create ticket
            $ticket = $this->createTicketForThisClient($newLineRequest->id);
            if ($ticket['success'] == 'Faild') {
                DB::rollBack();
                return response()->json([
                    'error' => true,
                    'message' => $ticket['message'],
                ], 500);
            }

            DB::commit();

            // Reload the request with relationships to get updated client and ticket data
            $newLineRequest->refresh();
            $newLineRequest->load(['client']);

            // Get the ticket token directly
            $ticketToken = \App\Models\Token::where('clientId', $newLineRequest->client_id)
                ->where('tokenCategory', 2)
                ->where('tokenCode', 58)
                ->first();

            $lineManager = Employee::find($request->line_manager_id);

            return response()->json([
                'error' => false,
                'message' => 'Line man assigned successfully!',
                'line_manager_id' => $newLineRequest->line_manager_id,
                'line_manager_name' => $lineManager ? $lineManager->name : '',
                'userid' => $newLineRequest->client->userid ?? null,
                'cid' => $newLineRequest->client->id ?? null,
                'ticket' => $ticketToken ? $ticketToken->token : null,
            ]);
        } catch (Exception $e) {
            dd($e->getMessage());
            DB::rollBack();
            return response()->json([
                'error' => true,
                'message' => 'Line man assignment failed: ' . $e->getMessage(),
            ], 500);
        }
    }

    public function generateUniqueUserId($name, $phone)
    {
        $lastFourDigits = substr($phone, -4);
        //remove space from name
        $name = str_replace(' ', '', $name);
        $baseUsername = strtolower($name) . $lastFourDigits;
        $username = $baseUsername;
        $suffix = 'a'; // Start with 'a' if conflict

        while (true) {
            $exists = Client::withTrashed()->where('userid', $username)->exists();

            if (!$exists) break;

            // Append/replace suffix character (a, b, c, ...)
            $username = $baseUsername . $suffix;
            $suffix++;  // PHP increments chars: 'a' -> 'b' -> 'c' ... 'z' -> 'aa'
        }

        return $username;
    }

    public function createPppoeUserFromConfirmLineRequest($id)
    {

        $newLineRequest = NewLineRequest::find($id);

        if ($newLineRequest->client_id) {
            $data = [
                'success' => 'Faild',
                'message' => 'Client already created',
            ];

            return $data;
        }

        if ($newLineRequest->pop_id == null) {
            $data = [
                'success' => 'Faild',
                'message' => 'Pop is not selected',
            ];

            return $data;
        }

        if ($newLineRequest->billing_date == null) {
            $data = [
                'success' => 'Faild',
                'message' => 'Billing date is not set',
            ];

            return $data;
        }

        $pop = Pop::with("nas")->find($newLineRequest->pop_id);

        if ($newLineRequest->confirm_activation_date == null) {
            $data = [
                'success' => 'Faild',
                'message' => 'Confirm activation date is not set',
            ];

            return $data;
        }

        if ($newLineRequest->confirm_activation_date > today()->endOfMonth()->addMonths(1)->format('Y-m-d')) {
            $data = [
                'success' => 'Faild',
                'message' => 'Confirm activation date is not set to more than 1 month',
            ];

            return $data;
        }

        if ($newLineRequest->marketing_user_id == null) {
            $data = [
                'success' => 'Faild',
                'message' => 'Marketing user is not assigned yet',
            ];

            return $data;
        }

        // get first part of name from new line request
        $first_part_of_name = explode(' ', $newLineRequest->name)[0];


        // create username with name and mobile number last four digit and first letter of name
        $user_id = $this->generateUniqueUserId($first_part_of_name, $newLineRequest->phone);
        if (!$user_id) {
            $data = [
                'success' => 'Faild',
                'message' => 'Something went wrong to generate unique user id',
            ];

            return $data;
        }
        if ($pop->subreseller == 'yes') {
            $sub_package = SubPackage::find($newLineRequest->sub_package_id);
            $package = $sub_package->package_id;
        } else {
            $package = $newLineRequest->package_id;
        }

        //create pppoe user
        $client = new Client();
        $client->pop_id = $pop->id;
        if ($pop->subreseller == 'yes') {
            $client->package_id = $package;
            $client->sub_package_id = $sub_package->id;
        } else {
            $client->package_id = $package;
        }
        $client->billing_cycle = Carbon::parse($newLineRequest->confirm_activation_date)->format('d');
        $client->userid  = $user_id;
        $client->password  =  44455;

        if (Carbon::parse($newLineRequest->confirm_activation_date)->format('Y-m-d')  > today()->endOfMonth()->format('Y-m-d')) {
            $client_expire_date = Carbon::parse($newLineRequest->confirm_activation_date)->addMonth()->format('Y-m-d 00:00:00');
        } else {
            $client_expire_date = Carbon::parse($newLineRequest->confirm_activation_date)->format('Y-m-d 00:00:00');
        }

        $client->expire_date = $client_expire_date;

        $client->clients_status = 'deactive';
        $client->client_approval = 'pending';

        $client->payment_dadeline = 0;
        $client->created_by = auth()->user()->id;
        $client->marketing_employee_id = $newLineRequest->marketing_user_id;

        $client->save();

        // dd($client);
        // create client info
        $client_info = new Clientsinfo();
        $client_info->client_id = $client->id;
        $client_info->clients_name = $newLineRequest->name;
        $client_info->contact_no = $newLineRequest->phone;
        $client_info->national_id = $newLineRequest->nid;
        $client_info->father_name = $newLineRequest->father_name;
        $client_info->mother_name = $newLineRequest->mother_name;
        $client_info->client_latitude = $newLineRequest->lat;
        $client_info->client_longitude = $newLineRequest->long;
        $client_info->cat_length = $newLineRequest->fiver_miter;
        if ($pop->bill_generate == 'yes') {
            $client_info->otc = $newLineRequest->otc ?? 0;
            $client_info->otc_due = $newLineRequest->otc ?? 0;
        } else {
            $client_info->otc = 0;
            $client_info->otc_due = 0;
        }
        $client_info->other_contact = $newLineRequest->alt_phone;
        $client_info->national_id = $newLineRequest->nid;
        $client_info->division = $newLineRequest->division_id;
        $client_info->district = $newLineRequest->district_id;
        $client_info->upazila = $newLineRequest->upazila_id;
        $client_info->thana = $newLineRequest->thana_id;
        $client_info->email = $newLineRequest->email;
        $client_info->source_name = $newLineRequest->source_id;
        $client_info->save();



        if ($pop->bill_generate == 'yes') {
            $package = Packages::find($package);
            // add customer account
            CustomerAccount::updateCustomrAccount($client->id, 0, 0, 0, 0);

            //check new customer entry bill generated or not
            if (Carbon::parse($newLineRequest->confirm_activation_date)->format('d') != $newLineRequest->billing_date) {
                $activationDate = Carbon::parse($newLineRequest->confirm_activation_date);

                $expire_date_as_per_as_billing_date = $activationDate->copy()->day($newLineRequest->billing_date);

                if (Carbon::parse($expire_date_as_per_as_billing_date)->format('Y-m-d') <= Carbon::parse($newLineRequest->confirm_activation_date)->format('Y-m-d')) {
                    $client_expire_date = Carbon::parse($expire_date_as_per_as_billing_date)->addMonth()->format('Y-m-d 00:00:00');
                } else {
                    $client_expire_date = Carbon::parse($expire_date_as_per_as_billing_date)->format('Y-m-d 00:00:00');
                }

                // calculate the difference between the confirm activation date and the expire date as per the billing date
                $difference_in_days = Carbon::parse($newLineRequest->confirm_activation_date)->diffInDays(Carbon::parse($client_expire_date));
                if ($difference_in_days > 0) {
                    $difference_in_days = $difference_in_days + 1;
                    $new_line_cost = PriceCalculate::calculate($activationDate->daysInMonth, $package->package_rate, $difference_in_days);
                    if ($new_line_cost > 0) {
                        CustomerAccount::updateCustomrAccount($client->id, $new_line_cost, 0, 0, $new_line_cost);
                        $bill_new_customer_entry = BillGenerateController::entryBillGenerate(
                            $client->id,
                            $new_line_cost,
                            'New Customer Entry',
                            'new',
                            $client_expire_date
                        );
                    }
                }

                $client->expire_date = $client_expire_date;
                $client->billing_cycle = Carbon::parse($client_expire_date)->format('d');
                $client->save();
            } else {
                $client_expire_date = Carbon::parse($newLineRequest->confirm_activation_date)->addMonth()->format('Y-m-d 00:00:00');
                $client->expire_date = $client_expire_date;
                $client->billing_cycle = Carbon::parse($client_expire_date)->format('d');
                $client->save();

                // generate a monthly bill
                $first_day_of_next_month = $newLineRequest->confirm_activation_date;
                CustomerAccount::updateCustomrAccount($client->id, $package->package_rate, 0, 0, $package->package_rate);
                BillGenerateController::entryBillGenerate(
                    $client->id,
                    $package->package_rate,
                    'Monthly Bill Generate',
                    'monthly',
                    $client_expire_date,
                    0,
                    0,
                    null,
                    null,
                    Carbon::parse($first_day_of_next_month)->format('Y-m-d 00:00:00')

                );
            }

            // if otc has
            if ($newLineRequest->otc > 0) {
                //add due to customer account
                CustomerAccount::updateCustomrAccount($client->id, $newLineRequest->otc, 0, 0, $newLineRequest->otc);
                $this->otcBillgenerateAndProcess($user_id, $client->id, $newLineRequest->otc);
            }

            // add a monthly bill
            if (Carbon::parse($client_expire_date) < today()->endOfMonth()) {
                //add due to customer account
                CustomerAccount::updateCustomrAccount($client->id, $package->package_rate, 0, 0, $package->package_rate);
                BillGenerateController::entryBillGenerate(
                    $client->id,
                    $package->package_rate,
                    'Monthly Bill Generate',
                    'monthly',
                    $client_expire_date
                );
            }
        }

        $newLineRequest->client_id = $client->id;
        $newLineRequest->save();

        $data = [
            'success' => 'Success',
            'message' => 'Client created successfully',
        ];

        return $data;
    }

    public function otcBillgenerateAndProcess($userid, $client_id, $otc)
    {
        if ($otc != null and $otc > 0) {

            BillGenerateController::entryBillGenerate(
                $client_id,
                $otc,
                'New Customer OTC',
                'otc',
                ''
            );

            if (checkSettings('otc-auto-due') == 'disable') {

                $income_type = IncomeHead::find(2);
                $income = new Income();
                $income->name = $income_type->name;
                $income->date = now();
                $income->amount = $otc;
                $income->description = 'Bill Payment for customer : ' . $userid;
                $income->incomeHead = $income_type->id;
                $income->save();

                (new PaymentController)->newUserOTCPaymentProcess($client_id, $otc, $income->id);
            }
        }
    }


    public function getMarketingUsers(Request $request)
    {

        $lineRequest = NewLineRequest::find($request->id);
        if (!$lineRequest) {
            return response()->json([
                'error'   => true,
                'message' => 'Line request not found.',
            ], 404);
        }

        // Guard: find source
        $source = ClientSource::find($lineRequest->source_id);
        if (!$source) {
            return response()->json([
                'error'   => true,
                'message' => 'Source not found.',
            ], 404);
        }

        // Base query shared by both branches
        $baseQuery = Employee::where('status', 'active')->orderBy('name', 'asc');

        if ($source->transmission_team_status == 'No' || $lineRequest->created_by == null) {
            // Only return employees whose departments are NOT voucer_software_id = 9
            // all the employee hase department 9
            $voucher_department_9 = Departmant::where('voucer_software_id', 8)->first();

            // all ther employes that are not have voucher_software_id = 9
            $employees = $baseQuery->whereHas('departments', function ($query) use ($voucher_department_9) {
                $query->where('department_id', '!=', $voucher_department_9->id);
            })->get();
        } else {
            $employees = $baseQuery->get();
        }

        return response()->json([
            'error' => false,
            'data'  => $employees,
        ]);
    }

    public function createTicketForThisClient($id)
    {
        $newLineRequest = NewLineRequest::find($id);

        if ($newLineRequest->client_id === null) {
            $data = [
                'success' => 'Faild',
                'message' => 'Client not created yet',
            ];

            return $data;
        }

        if ($newLineRequest->line_manager_id === null) {
            $data = [
                'success' => 'Faild',
                'message' => 'Line man is not assigned yet',
            ];

            return $data;
        }

        // check if ticket already exists
        $ticket = Token::where('clientId', $newLineRequest->client_id)->where('tokenCategory', 2)->where('tokenCode', 58)->first();
        if ($ticket) {
            $data = [
                'success' => 'Faild',
                'message' => 'Ticket already exists',
            ];

            return $data;
        }

        $request = new Request([
            'clientId'        => $newLineRequest->client_id,
            'tokenCategory'   => 2,
            'tokenCode'       => 58,
            'description'     => 'Ticket for new line request: ' . $newLineRequest->name . ' - ' . $newLineRequest->phone,
            'reportedBy'      => auth()->user()->name,
            'reporterContact' => $newLineRequest->phone,
            'token_type'      => 'Physical',
            'token_source'    => 21,
        ]);

        $token_class = new TokenClass();
        $token = $token_class->saveToken($request);

        $token_sms = new TokenSms();

        if (checkSettings('token-create-sms') == 'enable') {
            try {
                $token_sms->createSms($token->id);
            } catch (Exception $e) {
            }
        }

        if (checkSettings('token-create-sms') == 'enable') {
            try {
                $token_sms->assignTokenSms($token->id);
            } catch (Exception $e) {;
            }
        }

        $data = [
            'success' => 'Success',
            'message' => 'Ticket created successfully',
        ];

        return $data;
    }

    public function confirmLineRequestSummary()
    {
        // Show all pending confirm line by POP and delivery date wise
        $pendingConfirmLine = NewLineRequest::with('pop')
            ->where('status', 'pending')
            ->where('line_status', 'confirm_request')
            ->whereNotNull('delivery_date')
            ->get();

        $pops = Pop::conditionalList()->pluck('id', 'popname')->toArray();

        // Build day columns: from today through next month's first 5 days
        $days_difference = Carbon::parse(today())->diffInDays(Carbon::parse(today()->copy()->addMonth()->subDay()));
        // dd($days_difference);
        $dayColumns = [];
        $firstDate = Carbon::today();
        for ($i = 0; $i <= $days_difference; $i++) {
            $date = $firstDate->copy()->addDays($i);
            $dayColumns[] = [
                'label' => $date->format('d'),
                'date'  => $date->format('Y-m-d'),
            ];
        }

        // Summary by POP: previous count (before first day) + count per day
        $summaryByPop = [];
        foreach ($pendingConfirmLine->groupBy('pop_id') as $popId => $items) {
            $first = $items->first();
            $popName = $first->pop->popname ?? 'N/A';
            $previous = $items->filter(function ($item) use ($firstDate) {
                $d = Carbon::parse($item->delivery_date)->startOfDay();
                return $d->lt($firstDate);
            })->count();
            $countsByDate = [];
            foreach ($dayColumns as $col) {
                $countsByDate[$col['date']] = $items->filter(function ($item) use ($col) {
                    $d = Carbon::parse($item->delivery_date)->format('Y-m-d');
                    return $d === $col['date'];
                })->count();
            }
            $summaryByPop[] = [
                'pop_id'   => $popId,
                'popname'  => $popName,
                'previous' => $previous,
                'counts'   => $countsByDate,
            ];
        }

        // Sort by POP name ascending
        $summaryByPop = collect($summaryByPop)->sortBy('popname')->values()->all();
        // dd($summaryByPop);

        return view('clients.newclient.summary', [
            'pendingConfirmLine'       => $pendingConfirmLine,
            'pops'                     => $pops,
            'dayColumns'               => $dayColumns,
            'summaryByPop'             => $summaryByPop,
        ]);
    }

    public function newLineRequestSummary()
    {
        $baseQuery = NewLineRequest::where(function ($query) {
            $query->where('survey_status', '!=', 'Not Coverage')
                ->orWhereNull('survey_status');
        })
            ->whereNull('marketing_done_by')
            ->where('line_status', 'new_request');

        // --- Simple counts via DB (avoid loading full records) ---
        $total = (clone $baseQuery)->count();

        $baseQueryCanceled = NewLineRequest::where('marketing_done', 5)
            ->where('survey_status', 'Coverage')
            ->count();

        $counts = (clone $baseQuery)
            ->selectRaw("
                COUNT(CASE WHEN assigned_to IS NULL THEN 1 END)  AS un_assign_count,
                COUNT(CASE WHEN lock_by IS NOT NULL THEN 1 END)  AS total_lock_count,
                COUNT(CASE WHEN support_done = 0 THEN 1 END)     AS total_un_support_count,
                COUNT(CASE WHEN multi_router = 'Yes' THEN 1 END) AS multi_router_yes,
                COUNT(CASE WHEN support_done = 1 AND support_done_by IS NULL THEN 1 END) AS support_done_yes
            ")
            ->first();

        $follow_up_lines_today = NewLineRequest::where(function ($query) {
            $query->where('survey_status', '!=', 'Not Coverage')
                ->orWhereNull('survey_status');
        })
            ->where('line_status', 'new_request')
            ->whereNotNull('follow_up_date')
            ->where('follow_up_date', '<=', Carbon::today()->format('Y-m-d'))
            ->count();

        // --- Load full records only once, with necessary relations ---
        $new_line_requests = (clone $baseQuery)
            ->with(['pop', 'user', 'assign', 'ClientSource', 'latestEditLog', 'remarksList'])
            ->orderBy('updated_at', 'desc')
            ->get();

        // --- Pending: has a latestEditLog ---
        $pending_collection = $new_line_requests->filter(fn($item) => $item->latestEditLog !== null);
        $total_pending_new_line_request = $pending_collection->count();
        $pending_ids = $pending_collection->pluck('id')->toArray();

        // --- Over 48h pending ---
        $pending_over_48h = $pending_collection->filter(
            fn($item) => $item->latestEditLog->created_at->diffInHours(now()) > 48
        );
        $ids_over_48h = $pending_over_48h->pluck('id')->toArray();

        $logs = LogHistory::whereIn('log_id', $ids_over_48h)
            ->where('log_type', 'new_line_request_update')
            ->get();

        $countByUser = $logs->groupBy('edit_by')
            ->map(fn($group) => $group->unique('log_id')->count());

        $total_48h_pending = $countByUser->sum();

        // --- Load all needed users in one query ---
        $allUserIds = collect($countByUser->keys())
            ->merge($pending_collection->pluck('assigned_to')->filter())
            ->unique()
            ->toArray();

        $allUsers = User::whereIn('id', $allUserIds)->get()->keyBy('id');

        $user_wise_pending_count = $countByUser->map(fn($count, $id) => [
            'user_id'   => $id,
            'user_name' => $allUsers[$id]->name ?? 'Unknown',
            'count'     => $count,
        ])->sortByDesc('count')->values();

        $employee_wise_pending_with_users = $pending_collection
            ->groupBy('assigned_to')
            ->map(fn($group, $userId) => [
                'user'  => $allUsers->get($userId),
                'count' => $group->count(),
            ])
            ->filter(fn($item) => $item['user'] !== null)
            ->sortByDesc('count')
            ->values();

        // --- Today's records with no edit log ever + no remarks ever (10min / 60min thresholds) ---
        $start = today()->startOfDay();
        $end   = today()->endOfDay();

        $todayNoLogAndNoRemarks = $new_line_requests->filter(
            fn($item) => $item->latestEditLog === null
                && $item->remarksList->isEmpty()
                && $item->created_at->between($start, $end)
        );

        $total_10_minutes = $todayNoLogAndNoRemarks->filter(
            fn($item) => $item->created_at->diffInMinutes(now()) > 10
        )->count();

        $total_60_minutes = $todayNoLogAndNoRemarks->filter(
            fn($item) => $item->created_at->diffInMinutes(now()) > 60
        )->count();

        return view('clients.newclient.newLineSummary', [
            'page_title'                     => 'New Line Request Summary',
            'total'                          => $total,
            'multi_router_yes'               => $counts->multi_router_yes,
            'support_done_yes'               => $counts->support_done_yes,
            'total_10_minutes'               => $total_10_minutes,
            'total_60_minutes'               => $total_60_minutes,
            'un_assign_count'                => $counts->un_assign_count,
            'total_lock_count'               => $counts->total_lock_count,
            'total_un_support_count'         => $counts->total_un_support_count,
            'total_pending_new_line_request' => $total_pending_new_line_request,
            'pending_ids'                    => $pending_ids,
            'employee_wise_pending'          => $employee_wise_pending_with_users,
            'total_canceled'                 => $baseQueryCanceled,
            'user_wise_pending_count'        => $user_wise_pending_count,
            'total_48h_pending'              => $total_48h_pending,
            'follow_up_lines_to_day_or_samaller_then_to_day' => $follow_up_lines_today,
        ]);
    }
}
