<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Clients\PendingClientController;
use App\Imports\ClientDelete;
use App\Imports\OnlinePaymentRechargeInReseller;
use App\Imports\PaymentToResellerBalanceLogAndRecharge;
use App\Models\Balance;
use App\Models\BillGenerate;
use App\Models\Billpayment;
use App\Models\Client;
use App\Models\Clientsinfo;
use App\Models\CustomerAccount;
use App\Models\ExpenseHead;
use App\Models\GlobalSettings;
use App\Models\IncomeHead;
use App\Models\Permission;
use App\Models\Pop;
use App\Models\ReselleBalanceLogReport;
use App\Models\Reseller;
use App\Models\ResellerSmsGetway;
use App\Models\SmsSetting;
use App\Models\User;
use App\Models\User_log;
use App\Services\ClientApproveService;
use App\Services\ExpirationService;
use Brian2694\Toastr\Facades\Toastr;
use Carbon\Carbon;
use Exception;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Spatie\Permission\Models\Role;

class FixCommonIssueController extends Controller
{

    public function fixRokibulViName()
    {
        $user = User::where('email', 'rokibulhasan.356@gmail.com')->first();
        $user->name = 'System';
        $user->save();
    }

    public function changePermissionName()
    {
        $permission = Permission::where('name', 'change-package-without-cost')->first();
        if ($permission) {
            $permission->name = 'batch-package-change-without-cost';
            $permission->save();
        }
    }

    public function fixedDuplicateMoneyReceiptNumber()
    {


        $duplicateReceiptNumbers = Billpayment::groupBy('money_receipt_number')
            ->havingRaw('COUNT(*) > 1')
            ->get();
        // dd($duplicateReceiptNumbers->count());
        foreach ($duplicateReceiptNumbers as $duplicateReceiptNumber) {
            $billPayments = Billpayment::where('money_receipt_number', $duplicateReceiptNumber->money_receipt_number)->get();
            $count = 0;
            foreach ($billPayments as $billPayment) {
                $count++;
                if ($count > 1) {

                    // $billPayment->money_receipt_number = $billPayment->money_receipt_number . '-' . $count;
                    // $billPayment->save();
                    DB::table('bill_payments')
                        ->where('id', $billPayment->id) // Assuming 'id' is the primary key column
                        ->update([
                            'money_receipt_number' => DB::raw("CONCAT(money_receipt_number, '-$count')")
                        ]);
                }
            }
        }
    }

    public function setAllPermanentDiscountNullValueToZero()
    {
        DB::table('clients')
            ->whereNull('parmanent_discount')
            ->update(['parmanent_discount' => 0]);
    }

    public function deletePreviousMonthBIllForDeActiveCustomer()
    {
        $start = today()->format('Y-m-d 00:00:00');
        $end = today()->endOfMonth()->format('Y-m-d 23:59:59');
        $clients = Client::with('customerAccount', 'packages')->where('client_approval', 'approved')->where('clients_status', 'expired')
            ->where('expire_date', '2025-02-27')->get();
        // dd($clients->count());
        $allClient = [];
        foreach ($clients as $client) {
            if (isset($client->customerAccount)) {
                $due = $client->customerAccount->dueAmount ?? 0;
                if ($due >= 0) {

                    $bill =  BillGenerate::where('client_id', $client->id)
                        ->whereBetween('created_at', [$start, $end])
                        ->where('billing_type', 'monthly')
                        ->first();

                    if ($bill) {
                        DB::beginTransaction();
                        try {
                            array_push($allClient, $client->id);
                            $clientAccount = CustomerAccount::where('client_id', $bill->client_id)->first();

                            $clientAccount->decrement('dueAmount', $bill->bill_amount);
                            $clientAccount->decrement('totalBillAmount', $bill->bill_amount);

                            $description = $bill->billing_type . ' bill deleted: ' . $bill->bill_amount;
                            $bill->description = $description . ' | Previous description : ' . $bill->description;
                            $bill->bill_amount = 0;
                            $bill->paid_amount = 0;
                            $bill->parmanent_discount = 0;
                            $bill->billing_type = 'deleted';
                            $bill->deleted_at = Carbon::now();
                            $bill->deleted_by = auth()->user()->id ?? 1;
                            $bill->save();
                            DB::commit();
                        } catch (\Exception $e) {
                            dd($e);
                        }
                    }
                }
            } else {
                // dd($client->toArray());
            }
        }

        // dd($allClient);
    }

    public function addCustomerAccountIfNotExit()
    {
        $clients = Client::with('pops')->where('client_approval', 'approved')->get();

        foreach ($clients as $client) {
            // dd($client->reseller, $client);
            if ($client->pops->billable == 'yes') {
                $checkCustomerAccount = CustomerAccount::where('client_id', $client->id)->get()->count();
                if ($checkCustomerAccount <= 0) {
                    CustomerAccount::create([
                        'client_id'       => $client->id,
                        'totalPaid'       => 0,
                        'dueAmount'       => 0,
                        'totalDiscount'   => 0,
                        'totalBillAmount' => 0
                    ]);
                }
            }
        }
    }

    public function addBandwidthBuyExpenseHeadInExpenseHeadTable()
    {
        $data = [
            ['name' => 'BandWith Buy',]

        ];

        foreach ($data as $d) {

            $expense_head = ExpenseHead::where('name', $d['name'])->get()->count();
            if ($expense_head == 0) {
                $new_expense_head = new ExpenseHead();
                $new_expense_head->name = $d['name'];
                $new_expense_head->save();
            }
        }
    }
    public function addBandwidthSaleInIncomeHeadTable()
    {
        $data = [
            ['name' => 'BandWith Sale',]

        ];

        foreach ($data as $d) {

            $expense_head = IncomeHead::where('name', $d['name'])->get()->count();
            if ($expense_head == 0) {
                $new_income_head = new IncomeHead();
                $new_income_head->name = $d['name'];
                $new_income_head->save();
            }
        }
    }

    public function setAllIdToUniqueIdInResellerBalanceLog()
    {
        $allResellerBalanceLog = ReselleBalanceLogReport::get();
        foreach ($allResellerBalanceLog as $log) {
            $log->uniqueId = $log->id;
            $log->save();
        }
    }

    public function addNewSmsToResellerSmsSetting()
    {
        // Define all new SMS templates once
        $newSmsTemplates = [
            [
                'sms_type' => 'other_reseller_customer_recharge',
                'send_sms' => 'No',
                'sms_body' => 'প্রিয় গ্রাহক ({c_name}), আপনার {month} মাস পর্যন্ত বিল সঠিক ভাবে সংগৃহিত হয়েছে। {reseller_name} এর সাথে থাকার জন্য ধন্যবাদ।  যেকোনো তথ্যের জন্য যোগাযোগ {reseller_mobile} এই নাম্বারে|',
            ],
            [
                'sms_type' => 'other_reseller_customer_reminder',
                'send_sms' => 'No',
                'sms_body' => 'সম্মানিত গ্রাহক  আপনার {c_name}। ইন্টারনেট বিল পরিশোধের শেষ তারিখ {c_expire_date} দয়া করে আপনার বিলটি পরিশোধ করুন। ',
                'days_before' => '2',
            ],
            [
                'sms_type' => 'other_reseller_customer_expire',
                'send_sms' => 'No',
                'sms_body' => 'প্রিয় গ্রাহক {c_name} আপনার ইন্টারনেট বিল পরিশোধ করার সময়সীমা অতিক্রম করেছে। নিরবিচ্ছিন্ন সংযোগ পেতে বকেয়া টাকা পরিশোধ করুন।',
            ],
            [
                'sms_type' => 'other_reseller_customer_create',
                'send_sms' => 'No',
                'sms_body' => '{reseller_name} এ আপনাকে স্বাগতম। আপনার User Id: {c_username} ও Password: {c_password} সংরক্ষন করে রাখোন।',
            ],
            [
                'sms_type' => 'own_reseller_customer_create',
                'send_sms' => 'No',
                'sms_body' => '{reseller_name} এ আপনাকে স্বাগতম। আপনার User Id: {c_username} ও Password: {c_password} সংরক্ষন করে রাখোন।',
            ],
            [
                'sms_type' => 'own_reseller_customer_payment',
                'send_sms' => 'No',
                'sms_body' => 'প্রিয় গ্রাহক ({c_name}), CID-{c_id} আপনার বিল সঠিক ভাবে সংগৃহিত হয়েছে। সংগৃহিত টাকার পরিমান- TK {PaymentAmount} | ',
            ],
            [
                'sms_type' => 'own_reseller_customer_reminder',
                'send_sms' => 'No',
                'sms_body' => 'সম্মানিত গ্রাহক আইডি - {c_id}. ইন্টারনেট বিল {TotalDue} পরিশোধের শেষ তারিখ {c_deadline} দয়া করে আপনার বিলটি পরিশোধ করুন। ',
                'days_before' => '2',
            ]
        ];

        $resellers = Reseller::get();

        foreach ($resellers as $reseller) {
            // Check if Reseller already has an SMS Gateway
            $thisResellerSmsGateway = ResellerSmsGetway::where('reseller_id', $reseller->id)->first();

            if ($thisResellerSmsGateway) {
                // Update existing SMS Gateway
                $previousSmsSettings = json_decode($thisResellerSmsGateway->sms_formate, true) ?? [];

                // Get all existing sms types
                $existingSmsTypes = collect($previousSmsSettings)->pluck('sms_type')->toArray();

                // Filter new templates and add those that don't exist already
                $updatedSmsSettings = array_merge($previousSmsSettings, array_filter($newSmsTemplates, function ($newSms) use ($existingSmsTypes) {
                    return !in_array($newSms['sms_type'], $existingSmsTypes);
                }));

                $thisResellerSmsGateway->sms_formate = json_encode($updatedSmsSettings);
                $thisResellerSmsGateway->save();
            } else {
                // Create a new SMS Gateway for the reseller
                $newSmsGateway = new ResellerSmsGetway();
                $newSmsGateway->reseller_id = $reseller->id;
                $newSmsGateway->sms_formate = json_encode($newSmsTemplates);
                $newSmsGateway->save();
            }
        }
    }

    public function updateWelcomeAndBillpaySms()
    {
        // print_r('updateWelcomeAndBillpaySms');
        $smsSetting = SmsSetting::get();

        foreach ($smsSetting as $sms) {
            if ($sms->tamplate_name == 'welcome') {
                $check = json_decode($sms->tamplate_body);
                if ($check === null || !isset($check->sms_body)) {
                    $w_sms_body = $sms->tamplate_body;

                    $w_setting = GlobalSettings::where('name', 'new-user-create-sms')->first();
                    if ($w_setting->status == 'enable') {
                        $w_sendsms = "Yes";
                    } else {
                        $w_sendsms = "No";
                    }

                    $data = [
                        'sms_body' => $w_sms_body,
                        'sendsms' => $w_sendsms
                    ];
                    $welcome_sms_json = json_encode($data);

                    $sms->tamplate_body = $welcome_sms_json;
                    $sms->save();
                }
            }
            if ($sms->tamplate_name == 'bill_payment') {
                $check = json_decode($sms->tamplate_body);
                if ($check === null || !isset($check->sms_body)) {
                    $b_sms_body = $sms->tamplate_body;
                    $b_setting = GlobalSettings::where('name', 'bill-payment-sms')->first();
                    if ($b_setting->status == 'enable') {
                        $b_sendsms = "Yes";
                    } else {
                        $b_sendsms = "No";
                    }

                    $data = [
                        'sms_body' => $b_sms_body,
                        'sendsms' => $b_sendsms
                    ];

                    $bill_sms_json = json_encode($data);

                    $sms->tamplate_body = $bill_sms_json;
                    $sms->save();
                }
            }
        }
    }

    public function syncAllClientRadiusExpiration(){
        $clients = Client::get()->pluck('userid')->toArray();
        foreach($clients as $client)
        {
            try{
                $newExpirationService = new ExpirationService();
                $newExpirationService->syncExpiration($client);
            }catch(Exception $e){

            }

        }
    }

    public function addAllManagerAndPopAccount()
    {
        $mangers = Reseller::get();

        foreach($mangers as $manager){
            $balance = Balance::where('type', 'reseller')
                  ->where('type_id', $manager->id)
                  ->get();

            if($balance->count() <=0 ){
                Balance::create([
                    'type' => "reseller",
                    'type_id' => $manager->id,
                    'amount'  => 0,
                ]);
            }

        }

        $pops = Pop::get();

        foreach($pops as $pop){
            $balance = Balance::where('type', 'pop')
                  ->where('type_id', $pop->id)
                  ->get();

            if($balance->count() <=0 ){
                Balance::create([
                    'type' => "pop",
                    'type_id' => $pop->id,
                    'amount'  => 0,
                ]);
            }

        }

    }

    public function fixedThanIssue()
    {
        DB::statement("UPDATE clientsinfo SET upazila = thana WHERE id > 0 AND thana REGEXP '^[0-9]+$';");
    }

    public function fixedPaymentNotDeductIssue()
    {
        return view('commonIssue.paymentIssue');
    }

    public function paymentIssueFixed(Request $request)
    {
        $file = $request->file('clientFile')->store('import');
        if ($request->clientFile != null) {
            $fileName = time() . '.' . $request->clientFile->extension();
            $request->clientFile->move(storage_path('app/public/clientFile'), $fileName);
        }

        (new PaymentToResellerBalanceLogAndRecharge)->import($file);
        Toastr::success("Client File Upload Successfully");
        return back();
    }

    public function onlinePaymentButNotRechargeInManager()
    {
        return view('commonIssue.onlineRechageNotRechargeInManager');
    }

    public function paymentNotRechargeResellerUpdate(Request $request)
    {
        $file = $request->file('clientFile')->store('import');
        if ($request->clientFile != null) {
            $fileName = time() . '.' . $request->clientFile->extension();
            $request->clientFile->move(storage_path('app/public/clientFile'), $fileName);
        }

        (new OnlinePaymentRechargeInReseller)->import($file);
        Toastr::success("Client File Upload Successfully");
        return back();
    }

    public function createRandomClient()
    {
        $totalClient = 25000;
        $data = Carbon::parse(today())->format('Y-m-d 00:00:00');
        for($i = 0; $i < $totalClient; $i++){
            $client = new Client();

            $client->userid = "vfcpJohn".$i;
            $client->password = "12345";
            $client->package_id = 10;


            $client->pop_id = 1;
            $client->billing_cycle = 10;
            $client->expire_date = $data;
            $client->required_cable = 0;
            $client->created_by = 1;
            $client->client_approval = "pending";
            $client->clients_status = "deactive";


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

            $firstNames = ['John', 'Alice', 'Robert', 'Emily', 'Michael', 'Sophia'];
            $lastNames = ['Smith', 'Johnson', 'Brown', 'Williams', 'Jones', 'Davis'];

            $randomFirstName = $firstNames[array_rand($firstNames)];
            $randomLastName = $lastNames[array_rand($lastNames)];

            $randomName = "$randomFirstName $randomLastName";

            $clientInfo = new Clientsinfo();
            $clientInfo->client_id = $client->id;
            $clientInfo->clients_name = "John Doe";

            $clientInfo->contact_no = "01700000001";

            $clientInfo->joinDate = $data;


            $clientInfo->save();
            $this->approveClients($client->id);
        }

    }

    public  function approveClients($id){

            $client = Client::find($id);



            $status = 'active';


            Client::where('id', $client->id)
                ->update([
                    'client_approval' => 'approved',
                    'clients_status' => $status,
                ]);


            $user_log = new User_log();

            $user_log->log_type = "id_approved";
            $user_log->user_id = 1;
            $user_log->details = "Client Created";
            $user_log->client_id = $client->id;
            $user_log->save();

            CustomerAccount::create([
                'client_id'       => $client->id,
                'totalPaid'       => 0,
                'dueAmount'       => 0,
                'totalDiscount'   => 0,
                'totalBillAmount' => 0
            ]);

    }


    public function deleteClientManagerAllInfo()
    {
        return view('commonIssue.allClientMangaerInfoDelete');
    }

    public function deleteClientManagerAllInfoUpdate(Request $request)
    {
        $file = $request->file('clientFile')->store('import');
        if ($request->clientFile != null) {
            $fileName = time() . '.' . $request->clientFile->extension();
            $request->clientFile->move(storage_path('app/public/clientFile'), $fileName);
        }

        (new ClientDelete)->import($file);
        Toastr::success("Client File Upload Successfully");
        return back();
    }

    public function addNewRoleForResellerBilling()
    {
        $permissions = [
            'reseller-client-extra-bill-add',
            'reseller-client-payment-add',
            'view-client-account-balance',
            'reseller-customer-payment-report',
            'reseller-due-customer-list',
            'reseller-billing-info',
        ];

        $role = Role::where('name', 'Reseller Billing')->first();

        if (!$role) {
            $role = Role::create(['name' => 'Reseller Billing', 'guard_name' => 'web']);
            
            foreach ($permissions as $permissionName) {
                $permission = Permission::where('name', $permissionName)->first();
                if ($permission) {
                    $rolePermissionExists = DB::table('role_has_permissions')
                        ->where('permission_id', $permission->id)
                        ->where('role_id', $role->id)
                        ->exists();

                    if (!$rolePermissionExists) {
                        DB::table('role_has_permissions')->insert([
                            'permission_id' => $permission->id,
                            'role_id' => $role->id,
                        ]);
                    }
                }
            }
        }

    }




}
