<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Facades\Route;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;
use App\Jobs\SendUrlTrackingKeyJob;
use App\Jobs\SendDynamicUrlTrackingKeyJob;
use App\Jobs\SendNumbersJob;
use App\Jobs\SendDynamicNumbersJob;
use App\Models\ManageRoute;
use App\Models\SenderDetail;
use App\Models\TemplateDetail;
use App\Models\MST_group;
use App\Models\ContactDetails;
use App\Models\SenderBlock;
use App\Models\SenderRouting;
use App\Models\SmsGateway;
use App\Models\PlanAssign;
use App\Models\RoutingPlan;
use App\Traits\FileUploadTrait;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\Storage;
use App\Exports\SmsReportExport;
use App\Exports\SmsGatewayReportExport;
use App\Exports\SendJobsSmsReportExport;
use App\Exports\CampaignSmsReportExport;
use App\Exports\SmsDownloadExport;
use App\Exports\SmsMisReportExport;
use App\Exports\UrlTrackingReportExport;
use App\Exports\MiscallSmsReportsExport;
use App\Exports\ErrorDescriptionReportsExport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Illuminate\Support\Facades\Cache;  
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Log;
use App\Models\User;

class SendMessage extends Model
{
    use SoftDeletes, FileUploadTrait;

    protected $table = 'send_messages';

    protected $fillable = [
        'message', 'userid', 'unicode_type', 'senderid', 'service_id', 'request_code',
        'campaign_id', 'campaign_name', 'is_picked', 'numbers_count', 'msg_credit',
        'charset', 'senderid_name', 'user_type', 'original_url', 'url_status',
        'pe_id', 'template_id', 'ip_address', 'method', 'form_type', 'cut_off_value',
        'route', 'job_id', 'send_json', 'cut_off', 'cut_off_throughput', 'total_cutting',
        'sent_at', 'isActive', 'isDeleted', 'created_by', 'updated_by', 'deleted_by', 'created_at', 'updated_at'
    ];

    public function numbers()
    {
        return $this->hasMany(SendNumber::class, 'message_id');
    }

    public static function getRoutesForUser($user)
    {
        if(!$user->route_IDs) 
        {
            return collect();
        }

        $routeIds = explode(',', $user->route_IDs);

        return ManageRoute::whereIn('id', $routeIds)
                ->select('id', 'route_name')
                ->where('isActive', 1)
                ->get();
    }

    public static function getSenderIdsForUser($user)
    {
        return SenderDetail::where('user_ID', $user->id)
                ->select('id', 'sender_ID')
                ->where('isActive', 1)
                ->get();
    }

    public static function getTemplateDetails($user, $senderId, $requestedUserId = null)
    {
        $query = TemplateDetail::query();

        $query->where('isActive', 1);

        if($user->role_ID == 2 || $user->role_ID == 3 || $user->role_ID == 4) 
        {
            if($requestedUserId !== null) 
            {
                $query->where('user_ID', $requestedUserId);
            } 
            else 
            {
                $query->where('user_ID', $user->id);
            }
        } 
        else 
        {
            $query->where('user_ID', $user->id);
        }

        return $query->whereJsonContains('sender_ID', (int) $senderId)
                    ->select('id', 'template_ID', 'template_name', 'template_data', 'character_type')
                    ->get()
                    ->map(function ($item) {
                        $item->template_url = 'https://b1y.info/abcxyz/xxxxxxx';
                        return $item;
                    });
    }

    public static function getGroupDetails($user)
    {
        return MST_group::where('user_ID', $user->id)
                ->select('id', 'group_name')
                ->where('isActive', 1)
                ->get();
    }

    public static function getNumbersCountFromGroup(array $request, $user): int
    {
        return ContactDetails::whereIn('group_ID', $request['group_IDs'])->where('user_ID', $user->id)->count('contact_no');
    }

    public static function getNumbersCountFromCsv($request, $user)
    {
        // Expecting $request['csv_file'] to be an instance of UploadedFile (BulkSmsRequest should validate)
        if (empty($request['csv_file']) || !($request['csv_file'] instanceof \Illuminate\Http\UploadedFile)) {
            return [];
        }
        // Reuse the same extension-aware parser used for the actual send (extractNumbersFromFile,
        // app/Helpers/Helpers.php), so the preview count always matches what will really be sent.
        return extractNumbersFromFile($request['csv_file']);
    }

    public static function uploadTrackingUrlImage(array $data)
    {
        $instance = new self();

        if(request()->hasFile('image_path') && request()->file('image_path') instanceof UploadedFile) 
        {
            $file = request()->file('image_path');
            $data['image_path'] = $instance->uploadFile(
                $file,
                ['jpg', 'jpeg', 'png', 'svg', 'webp'],
                2 * 1024 * 1024,
                'tracking-url-images'
            );
        }

        $value = $data['image_path'];

        $relativePath = str_replace('/storage', 'public', parse_url($value, PHP_URL_PATH));
        
        return url(Storage::url($relativePath));
    }

    private static function isValidIndianPhoneNumber($number): bool
    {
        $cleanNumber = preg_replace('/[^0-9+]/', '', trim($number));
        
        if(strpos($cleanNumber, '+91') === 0) 
        {
            $cleanNumber = substr($cleanNumber, 3);
        }
        elseif(strpos($cleanNumber, '91') === 0 && strlen($cleanNumber) > 10) 
        {
            $cleanNumber = substr($cleanNumber, 2);
        }
        elseif(strpos($cleanNumber, '0') === 0 && strlen($cleanNumber) > 10) 
        {
            $cleanNumber = substr($cleanNumber, 1);
        }
        
        if(strlen($cleanNumber) === 10 && preg_match('/^[6-9]\d{9}$/', $cleanNumber)) 
        {
            return true;
        }
        
        return false;
    }
    
    private static function extractAndValidateNumbers($request, $method): array
    {
        $validNumbers = [];
        $invalidNumbers = [];
        
        $numbersInput = !empty($request['numbers']) ? $request['numbers'] : ($request['mobile_numbers'] ?? null); 

        if($method === 'Numbers' && !empty($numbersInput))
        {
            $rawNumbers = preg_split('/[\n\r,;|]+/', trim($numbersInput), -1, PREG_SPLIT_NO_EMPTY);
            
            foreach($rawNumbers as $rawNumber) 
            {
                $number = trim($rawNumber);
                
                if(empty($number)) 
                {
                    continue;
                }
                
                if(self::isValidIndianPhoneNumber($number)) 
                {
                    $validNumbers[] = $number;
                }else 
                {
                    $invalidNumbers[] = $number;
                }
            }
        }
        elseif($method === 'Groups' && !empty($request->group_IDs)) 
        {
            $groupIds = $request->group_IDs;
            $contacts = ContactDetails::whereIn('group_ID', $groupIds)->pluck('contact_no')->toArray();
            
            foreach($contacts as $contact)
            {
                $number = trim($contact);
                
                if(empty($number)) 
                {
                    continue;
                }
                
                if(self::isValidIndianPhoneNumber($number)) 
                {
                    $validNumbers[] = $number;
                } 
                else 
                {
                    $invalidNumbers[] = $number;
                }
            }
        }
        elseif($method === 'CSV' && $request->hasFile('csv_file')) 
        {
            $file = $request->file('csv_file');
            $numbers = extractNumbersFromFile($file);
            
            foreach($numbers as $number) 
            {
                $number = trim($number);
                
                if(empty($number)) 
                {
                    continue;
                }
                
                if(self::isValidIndianPhoneNumber($number)) 
                {
                    $validNumbers[] = $number;
                } 
                else 
                {
                    $invalidNumbers[] = $number;
                }
            }
        }
        
        $validNumbers = array_unique($validNumbers);
        $validNumbers = array_values($validNumbers);
        
        return [
            'valid' => $validNumbers,
            'invalid' => $invalidNumbers,
            'total_valid' => count($validNumbers),
            'total_invalid' => count($invalidNumbers)
        ];
    }

    private static function extractAndValidateDynamicNumbers($numbers): array
    {
        $validNumbers = [];
        $invalidNumbers = [];

        if (!is_array($numbers)) {
            $numbers = [$numbers];
        }

        // OPTIMIZATION: Use array as hash map for O(1) duplicate checking instead of in_array O(n)
        $validNumbersMap = [];
        $invalidCount = 0;

        foreach($numbers as $rawNumber)
        {
            $number = trim($rawNumber);

            if(empty($number))
            {
                continue;
            }

            // Use your existing validation method
            if(self::isValidIndianPhoneNumber($number))
            {
                // Normalize the number for deduplication
                $normalizedNumber = self::normalizeDynamicPhoneNumber(preg_replace('/[^0-9]/', '', $number));

                // OPTIMIZATION: Use isset() instead of in_array() - O(1) vs O(n)
                if (!isset($validNumbersMap[$normalizedNumber])) {
                    $validNumbersMap[$normalizedNumber] = true;
                    $validNumbers[] = $normalizedNumber;
                }
            }
            else
            {
                // OPTIMIZATION: Skip collecting invalid numbers for large datasets to save memory
                // Just count them instead
                if (count($numbers) < 10000) {
                    $invalidNumbers[] = $number;
                } else {
                    $invalidCount++;
                }
            }
        }

        // Free map memory
        unset($validNumbersMap);

        return [
            'valid' => $validNumbers,
            'invalid' => $invalidNumbers,
            'total_valid' => count($validNumbers),
            'total_invalid' => count($invalidNumbers) + $invalidCount
        ];
    }

    // public static function createAndProcess($request, $user)
    // {
    //     // Fetch senderid_name from sender_details table
    //     $senderDetail = SenderDetail::where('id', $request->senderid)->first();
    //     $senderid_name = $senderDetail ? $senderDetail->sender_ID : null;

    //     // Fetch pe_id from sender_details table
    //     $pe_id = $senderDetail ? $senderDetail->principle_entity_ID : null;

    //     // Fetch sender_ID from sender_details table
    //     $sender_ID = $senderDetail ? $senderDetail->sender_ID : null;

    //     // Fetch template_ID from template_details table
    //     $templateDetail = TemplateDetail::where('template_ID', $request->template_ID)->first();
    //     $template_data = $templateDetail ? $templateDetail->template_data : null;
    //     $content_type_ID = $templateDetail ? $templateDetail->content_type_ID : null;

    //     //  Fetch start time and end time from selected route name
    //     $routeDetail = ManageRoute::where('route_name', $request->route_name)->first();

    //     if(!$routeDetail) 
    //     {
    //         return ['status' => false, 'message' => 'Selected route not found.'];
    //     }

    //     $route_id = $routeDetail->id;
    //     $DND_check_ID = $routeDetail->DND_check_ID;
    //     $start_time = $routeDetail->start_time;
    //     $end_time = $routeDetail->end_time;

    //     $currentTime = Carbon::now();

    //     try 
    //     {
    //         $startTime = Carbon::parse($start_time)->setDate($currentTime->year, $currentTime->month, $currentTime->day);
    //         $endTime = Carbon::parse($end_time)->setDate($currentTime->year, $currentTime->month, $currentTime->day);
    //     } 
    //     catch(\Exception $e) 
    //     {
    //         return ['status' => false, 'message' => 'Invalid time format for route start or end time.'];
    //     }

    //     if(!$currentTime->between($startTime, $endTime)) 
    //     {
    //         return ['status' => false, 'message' => 'Can not send message for selected route.'];
    //     }

    //     //  Fetch sender block details
    //     $senderBlockDetail  = SenderBlock::where('sender_ID', $sender_ID)
    //                         ->where('user_ID', $user->id)
    //                         ->where('isActive', 1)
    //                         ->first();

    //     if($senderBlockDetail) 
    //     {
    //         return ['status' => false, 'message' => 'Sender ID has been blocked. You cannot send message with this Sender ID.'];
    //     }

    //     //  Fetch sender routing details
    //     $senderRoutingDetail    = SenderRouting::where('sender_ID', $request->senderid)
    //                             ->where('user_ID', $user->id)
    //                             ->where('isActive', 1)
    //                             ->first();

    //     $gatewayNames = [];
    //     $gatewayDistribution = [];

    //     if(!$senderRoutingDetail) 
    //     {
    //         $planAssignDetail   = PlanAssign::where('user_ID', $user->id)
    //                             ->where('isActive', 1)
    //                             ->first();

    //         if($planAssignDetail)
    //         {
    //             $plan_ID = $planAssignDetail->plan_ID;

    //             $routingPlanDetail  = RoutingPlan::where('plan_ID', $plan_ID)
    //                                 ->where('route_ID', $route_id)
    //                                 ->where('isActive', 1)
    //                                 ->first();

    //             if($routingPlanDetail)
    //             {
    //                 $gateway_ID = $routingPlanDetail->gateway_ID;
    //                 $gatewayPercent = $routingPlanDetail->gateway_percent;

    //                 if(is_array($gateway_ID)) 
    //                 {
    //                     $smsGateways = SmsGateway::whereIn('id', $gateway_ID)->get(['id', 'gateway_name']);
                        
    //                     foreach($smsGateways as $index => $gateway) 
    //                     {
    //                         $gatewayNames[] = $gateway->gateway_name;

    //                         $gatewayDistribution[] = [
    //                             'id' => $gateway->id,
    //                             'name' => $gateway->gateway_name,
    //                             'percent' => isset($gatewayPercent[$index]) ? $gatewayPercent[$index] : 0
    //                         ];
    //                     }
    //                 } 
    //                 else 
    //                 {
    //                     $smsGateway = SmsGateway::find($gateway_ID);
                        
    //                     if($smsGateway) 
    //                     {
    //                         $gatewayNames[] = $smsGateway->gateway_name;

    //                         $gatewayDistribution[] = [
    //                             'id' => $smsGateway->id,
    //                             'name' => $smsGateway->gateway_name,
    //                             'percent' => 100
    //                         ];
    //                     }
    //                 }

    //                 $serviceIdValue = implode(',', $gatewayNames);
    //             }
    //             else
    //             {
    //                 return ['status' => false, 'message' => 'Incorrect route selection!'];
    //             }
    //         }
    //         else
    //         {
    //             return ['status' => false, 'message' => 'Incorrect route selection!'];
    //         }
    //     }
    //     else
    //     {
    //         $gateway_ID = $senderRoutingDetail->gateway_ID;
        
    //         $smsGateway = SmsGateway::find($gateway_ID);

    //         if($smsGateway) 
    //         {
    //             $gatewayNames[] = $smsGateway->gateway_name;

    //             $gatewayDistribution[] = [
    //                 'id' => $smsGateway->id,
    //                 'name' => $smsGateway->gateway_name,
    //                 'percent' => 100
    //             ];
    //         }

    //         $serviceIdValue = $smsGateway ? $smsGateway->gateway_name : '';
    //     }

    //     //  Credit Management
    //     $creditManageDetail = CreditManage::where('route_id', $route_id)
    //                         ->where('userid', $user->id)
    //                         ->where('isActive', 1)
    //                         ->first();

    //     if($creditManageDetail)
    //     {
    //         $balance_credit = $creditManageDetail->balance;
    //     }
    //     else
    //     {
    //         return ['status' => false, 'message' => 'No credit balance found for this route.'];
    //     }

    //     // Validate and extract numbers based on method
    //     $numberValidationResult = self::extractAndValidateNumbers($request, $request->method);
        
    //     $route = Route::currentRouteName();
        
    //     if($route === 'bulk_sms.sendMessages') 
    //     {
    //         // Check if we have any valid numbers
    //         if($numberValidationResult['total_valid'] === 0) 
    //         {
    //             return [
    //                 'status' => false, 
    //                 'message' => 'No valid Indian phone numbers found. Please check your input.'
    //             ];
    //         }
    //     }
        
    //     // Log invalid numbers (optional - for debugging)
    //     if($numberValidationResult['total_invalid'] > 0) 
    //     {
    //         // You can log these invalid numbers if needed
    //         Log::info('Invalid numbers detected', [
    //             'method' => $request->method,
    //             'invalid_count' => $numberValidationResult['total_invalid'],
    //             'invalid_numbers' => array_slice($numberValidationResult['invalid'], 0, 10)
    //         ]);
    //     }

    //     //  Calculate estimated message credit cost
    //     $charCount = mb_strlen($request->message);
    //     $isUnicode = preg_match('/[^\x00-\x7F]/', $request->message);
    //     $limit = $isUnicode ? 70 : 160;
    //     $msgCredit = ceil($charCount / $limit);
        
    //     //  Estimate number count based on method
    //     /* $estimatedNumberCount = 0;

    //     if($request['method'] === 'Numbers' && !empty($request['numbers'])) 
    //     {
    //         $numbersToProcess = preg_split('/[, \r\n]+/', trim($request->numbers), -1, PREG_SPLIT_NO_EMPTY);
    //         $numbersToProcess = array_map('trim', $numbersToProcess);
    //         $numbersToProcess = self::removeDuplicateNumbers($numbersToProcess);
    //         $estimatedNumberCount = count($numbersToProcess);
    //     }
    //     elseif($request->method === 'Groups' && !empty($request->group_IDs)) 
    //     {
    //         $groupIds = $request->group_IDs;
    //         $contacts = ContactDetails::whereIn('group_ID', $groupIds)->pluck('contact_no')->toArray();
    //         $numbers = array_unique(array_map('trim', $contacts));
    //         $numbers = self::removeDuplicateNumbers($numbers);
    //         $estimatedNumberCount = count($numbers);
    //     }
    //     elseif($request->method === 'CSV' && $request->hasFile('csv_file')) 
    //     {
    //         $file = $request->file('csv_file');
    //         $numbers = extractNumbersFromFile($file);
    //         $numbers = self::removeDuplicateNumbers($numbers);
    //         $estimatedNumberCount = count($numbers);
    //     }
        
    //     $totalCreditCost = $msgCredit * $estimatedNumberCount; */

    //     // Use only valid numbers count for credit calculation
    //     $validNumberCount = $numberValidationResult['total_valid'];
    //     $totalCreditCost = $msgCredit * $validNumberCount;
        
    //     //  Check if balance is sufficient
    //     if($totalCreditCost > $balance_credit) 
    //     {
    //         return ['status' => false, 'message' => 'Insufficient Balance'];
    //     }

    //     //  URL Status
    //     if(!empty($request->original_url))
    //     {
    //         $url_status = 1;
    //     }
    //     else
    //     {
    //         $url_status = 0;
    //     }

    //     if($route === 'bulk_sms.sendMessages') 
    //     {
    //         //  Template message validation
    //         $validationResult = self::validateStrictTemplate($template_data, $request->message);
            
    //         if($validationResult['status'] === false) 
    //         {
    //             return $validationResult;
    //         }

    //         $message = self::create([
    //             'route' => $request->route_name,
    //             'method' => $request->method,
    //             'campaign_name' => $request->campaign_name,
    //             'senderid' => $request->senderid,
    //             'template_id' => $request->template_ID,
    //             'message' => $template_data,
    //             'unicode_type' => $request->unicode_type,
    //             'original_url' => $request->original_url,
    //             'job_id' => generateRandomAlphaNumeric(15),
    //             'form_type' => 'Bulk SMS',
    //             'senderid_name' => $senderid_name,
    //             'pe_id' => $pe_id,
    //             'ip_address' => $request->ip(),
    //             'userid' => $user->id,
    //             'service_id' => $serviceIdValue,
    //             'url_status' => $url_status,
    //             'msg_credit' => $totalCreditCost,
    //             'numbers_count' => $validNumberCount,
    //             'created_by' => $user->id,
    //             'created_at' => now(),
    //             'updated_at' => null,
    //         ]);

    //         //  Deduct credit from balance
    //         $newBalance = $balance_credit - $totalCreditCost;
    //         $creditManageDetail->update(['balance' => $newBalance]);
            
    //         //  Create transaction log
    //         CampaignTransactionLog::create([
    //             'job_id' => $message->job_id,
    //             'campaign_name' => $request->campaign_name,
    //             'total_numbers' => $validNumberCount, /* $estimatedNumberCount */
    //             'total_credit' => $totalCreditCost,
    //             'previous_balance' => $balance_credit,
    //             'new_balance' => $newBalance,
    //             'created_by' => $user->id,
    //             'created_at' => now(),
    //             'updated_at' => null,
    //         ]);

    //         /* $numbersToProcess = [];

    //         if($request['method'] === 'Numbers' && !empty($request['numbers'])) 
    //         {
    //             $numbersToProcess = preg_split('/[, \r\n]+/', trim($request->numbers), -1, PREG_SPLIT_NO_EMPTY);
    //             $numbersToProcess = array_map('trim', $numbersToProcess);

    //             //  Remove duplicate mobile numbers
	// 	        $numbersToProcess = self::removeDuplicateNumbers($numbersToProcess);

    //             dispatch(new SendUrlTrackingKeyJob($numbersToProcess, $message, $user->id, $sender_ID));
    //             dispatch(new SendNumbersJob($numbersToProcess, $message, $user->id, $request->message, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));
    //         }

    //         if($request->method === 'Groups' && !empty($request->group_IDs)) 
    //         {
    //             $groupIds = $request->group_IDs;

    //             // Fetch numbers from contact_details
    //             $contacts = ContactDetails::whereIn('group_ID', $groupIds)->pluck('contact_no')->toArray();

    //             // Deduplicate & clean
    //             $numbers = array_unique(array_map('trim', $contacts));

    //             //  Remove duplicate mobile numbers
	// 	        $numbers = self::removeDuplicateNumbers($numbers);

    //             dispatch(new SendUrlTrackingKeyJob($numbers, $message, $user->id, $sender_ID));
    //             dispatch(new SendNumbersJob($numbers, $message, $user->id, $request->message, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));
    //         }

    //         if($request->method === 'CSV' && $request->hasFile('csv_file')) 
    //         {
    //             $file = $request->file('csv_file');
    //             $numbers = extractNumbersFromFile($file);

    //             //  Remove duplicate mobile numbers
	// 	        $numbers = self::removeDuplicateNumbers($numbers);

    //             dispatch(new SendUrlTrackingKeyJob($numbers, $message, $user->id, $sender_ID));
    //             dispatch(new SendNumbersJob($numbers, $message, $user->id, $request->message, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));
    //         } */

    //         // Use the pre-validated numbers from numberValidationResult
    //         $validNumbers = $numberValidationResult['valid'];
            
    //         // Remove duplicate mobile numbers (additional safety)
    //         $validNumbers = self::removeDuplicateNumbers($validNumbers);

    //         dispatch(new SendUrlTrackingKeyJob($validNumbers, $message, $user->id, $sender_ID));
    //         dispatch(new SendNumbersJob($validNumbers, $message, $user->id, $request->message, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));  

    //         return $message;
    //     }
    //     // else if($route === 'dynamic_sms.sendDynamicMessages')
    //     // {
    //     //     $messagesFromRequest = $request->input('messages');

    //     //     if(!is_array($messagesFromRequest) || empty($messagesFromRequest)) 
    //     //     {
    //     //         return [
    //     //             'status' => false,
    //     //             'message' => '',
    //     //             'errors' => ['messages' => ['Messages array is required and must be a valid array.']]
    //     //         ];
    //     //     }

    //     //     // Validate dynamic messages and numbers
    //     //     $validDynamicMessages = [];
    //     //     $invalidDynamicNumbers = [];

    //     //     foreach($messagesFromRequest as $index => $msg) 
    //     //     {
    //     //         if(!isset($msg['message'])) 
    //     //         {
    //     //             return [
    //     //                 'status' => false,
    //     //                 'message' => 'Message content missing for entry '.$index,
    //     //                 'errors' => ['messages.' . $index . '.message' => ['Message content is required.']]
    //     //             ];
    //     //         }

    //     //         if(!isset($msg['number'])) 
    //     //         {
    //     //             return [
    //     //                 'status' => false,
    //     //                 'message' => 'Phone number missing for entry '.$index,
    //     //                 'errors' => ['messages.' . $index . '.number' => ['Phone number is required.']]
    //     //             ];
    //     //         }

    //     //         // Validate phone number
    //     //         if(!self::isValidIndianPhoneNumber($msg['number'])) 
    //     //         {
    //     //             $invalidDynamicNumbers[] = $msg['number'];
    //     //             continue;
    //     //         }
                
    //     //         $validationResult = self::validateStrictTemplate($template_data, $msg['message']);

    //     //         if($validationResult['status'] === false) 
    //     //         {
    //     //             return $validationResult;
    //     //         }

    //     //         $validDynamicMessages[] = $msg;
    //     //     }

    //     //     // Check if we have any valid dynamic messages
    //     //     /* if(count($validDynamicMessages) === 0) 
    //     //     {
    //     //         return [
    //     //             'status' => false,
    //     //             'message' => 'No valid Indian phone numbers found in dynamic messages.'
    //     //         ];
    //     //     } */

    //     //     //  Calculate total credit cost for dynamic messages
    //     //     $totalCreditCost = 0;
            
    //     //     foreach($messagesFromRequest as $msg) 
    //     //     {
    //     //         $charCount = mb_strlen($msg['message']);
    //     //         $isUnicode = preg_match('/[^\x00-\x7F]/', $msg['message']);
    //     //         $limit = $isUnicode ? 70 : 160;
    //     //         $msgCredit = ceil($charCount / $limit);
    //     //         $totalCreditCost += $msgCredit;
    //     //     }

    //     //     if($totalCreditCost > $balance_credit) 
    //     //     {
    //     //         return ['status' => false, 'message' => 'Insufficient Balance'];
    //     //     }

    //     //     $message = self::create([
    //     //         'route' => $request->route_name,
    //     //         'campaign_name' => $request->campaign_name,
    //     //         'senderid' => $request->senderid,
    //     //         'template_id' => $request->template_ID,
    //     //         'message' => $template_data,
    //     //         'unicode_type' => $request->unicode_type,
    //     //         'original_url' => $request->original_url,
    //     //         'job_id' => generateRandomAlphaNumeric(15),
    //     //         'form_type' => 'Dynamic SMS',
    //     //         'senderid_name' => $senderid_name,
    //     //         'pe_id' => $pe_id,
    //     //         'ip_address' => $request->ip(),
    //     //         'userid' => $user->id,
    //     //         'service_id' => $serviceIdValue,
    //     //         'url_status' => $url_status,
    //     //         'msg_credit' => $totalCreditCost,
    //     //         'created_by' => $user->id,
    //     //         'created_at' => now(),
    //     //         'updated_at' => null,
    //     //     ]);

    //     //     //  Update credit balance
    //     //     $newBalance = $balance_credit - $totalCreditCost;
    //     //     $creditManageDetail->update(['balance' => $newBalance]);

    //     //     //  Create transaction log
    //     //     CampaignTransactionLog::create([
    //     //         'job_id' => $message->job_id,
    //     //         'campaign_name' => $request->campaign_name,
    //     //         'total_numbers' => count($messagesFromRequest),
    //     //         'total_credit' => $totalCreditCost,
    //     //         'previous_balance' => $balance_credit,
    //     //         'new_balance' => $newBalance,
    //     //         'created_by' => $user->id,
    //     //         'created_at' => now(),
    //     //         'updated_at' => null,
    //     //     ]);

    //     //     /* $numbersToCheck = array_column($request->messages, 'number');

    //     //     $uniqueNumbers = self::removeDynamicDuplicateNumbers($numbersToCheck);

    //     //     $filteredMessages = array_filter($request->messages, function ($msg) use ($uniqueNumbers) 
    //     //     {
    //     //         $normalizedNumber = self::normalizeDynamicPhoneNumber(preg_replace('/[^0-9]/', '', $msg['number']));

    //     //         foreach($uniqueNumbers as $uniqueNum) 
    //     //         {
    //     //             if(self::normalizeDynamicPhoneNumber(preg_replace('/[^0-9]/', '', $uniqueNum)) === $normalizedNumber) 
    //     //             {
    //     //                 return true;
    //     //             }
    //     //         }

    //     //         return false;
    //     //     });

    //     //     $filteredMessages = array_values($filteredMessages); */

    //     //     $filteredMessages = [];
    //     //     $processedNumbers = [];

    //     //     foreach($request->messages as $msg) 
    //     //     {
    //     //         $normalizedNumber = self::normalizeDynamicPhoneNumber(preg_replace('/[^0-9]/', '', $msg['number']));
                
    //     //         if(!in_array($normalizedNumber, $processedNumbers)) 
    //     //         {
    //     //             $filteredMessages[] = $msg;
    //     //             $processedNumbers[] = $normalizedNumber;
    //     //         }
    //     //     }

    //     //     dispatch(new SendDynamicUrlTrackingKeyJob($filteredMessages, $message, $user->id, $sender_ID));
    //     //     dispatch(new SendDynamicNumbersJob($filteredMessages, $message, $user->id, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));
    //     // }
    //    else if($route === 'dynamic_sms.sendDynamicMessages')
    //     {
    //         Log::info('🚀 Dynamic SMS process STARTED', [
    //             'user_id' => $user->id,
    //             'campaign_name' => $request->campaign_name,
    //             'route' => $route
    //         ]);

    //         $messagesFromRequest = $request->input('messages');

    //         Log::debug('📥 Messages received from request', [
    //             'messages_count' => is_array($messagesFromRequest) ? count($messagesFromRequest) : 'NOT_ARRAY',
    //             'sample_first_3' => is_array($messagesFromRequest) ? array_slice($messagesFromRequest, 0, 3) : $messagesFromRequest
    //         ]);

    //         if(!is_array($messagesFromRequest) || empty($messagesFromRequest)) 
    //         {
    //             Log::error('❌ Messages array is empty or not an array', [
    //                 'type' => gettype($messagesFromRequest),
    //                 'value' => $messagesFromRequest
    //             ]);
    //             return [
    //                 'status' => false,
    //                 'message' => '',
    //                 'errors' => ['messages' => ['Messages array is required and must be a valid array.']]
    //             ];
    //         }

    //         // Check if template data is valid
    //         Log::debug('🔍 Checking template data', [
    //             'template_data_exists' => isset($template_data),
    //             'template_data_type' => gettype($template_data),
    //             'template_data_length' => $template_data ? strlen($template_data) : 0,
    //             'template_data_sample' => $template_data ? substr($template_data, 0, 100) : 'NULL'
    //         ]);

    //         if (!$template_data || empty(trim($template_data))) {
    //             Log::error('❌ Template data is invalid or empty', [
    //                 'template_ID_from_request' => $request->template_ID,
    //                 'template_detail_found' => isset($templateDetail) ? 'YES' : 'NO'
    //             ]);
    //             return [
    //                 'status' => false,
    //                 'message' => 'Invalid template or template not found.'
    //             ];
    //         }

    //         $allNumbers = [];
    //         Log::info('📋 Starting message validation', ['total_messages' => count($messagesFromRequest)]);
            
    //         foreach ($messagesFromRequest as $index => $msg) 
    //         {
    //             Log::debug("🔍 Validating message #$index", [
    //                 'index' => $index,
    //                 'message_keys' => array_keys($msg),
    //                 'has_number' => isset($msg['number']),
    //                 'has_message' => isset($msg['message'])
    //             ]);

    //             if (!isset($msg['message'])) 
    //             {
    //                 Log::error("❌ Message content missing", ['index' => $index, 'message_data' => $msg]);
    //                 return [
    //                     'status' => false,
    //                     'message' => 'Message content missing for entry '.$index,
    //                     'errors' => ['messages.' . $index . '.message' => ['Message content is required.']]
    //                 ];
    //             }

    //             if (!isset($msg['number'])) 
    //             {
    //                 Log::error("❌ Phone number missing", ['index' => $index, 'message_data' => $msg]);
    //                 return [
    //                     'status' => false,
    //                     'message' => 'Phone number missing for entry '.$index,
    //                     'errors' => ['messages.' . $index . '.number' => ['Phone number is required.']]
    //                 ];
    //             }
                
    //             $allNumbers[] = $msg['number'];
                
    //             // Log progress for large datasets
    //             if (count($messagesFromRequest) > 10000 && ($index + 1) % 5000 === 0) {
    //                 Log::info("📊 Processing progress", [
    //                     'processed' => $index + 1,
    //                     'total' => count($messagesFromRequest),
    //                     'percentage' => round(($index + 1) / count($messagesFromRequest) * 100, 2) . '%'
    //                 ]);
    //             }
    //         }

    //         Log::info('✅ Basic validation completed', [
    //             'total_numbers_collected' => count($allNumbers),
    //             'unique_numbers_count' => count(array_unique($allNumbers))
    //         ]);

    //         // First validate all numbers
    //         Log::info('🔢 Starting number validation');
    //         $numberValidationResult = self::extractAndValidateDynamicNumbers($allNumbers);
            
    //         Log::info('📊 Number validation results', [
    //             'total_received' => count($messagesFromRequest),
    //             'valid_numbers' => $numberValidationResult['total_valid'],
    //             'invalid_numbers' => $numberValidationResult['total_invalid'],
    //             'sample_invalid' => array_slice($numberValidationResult['invalid'], 0, 5)
    //         ]);

    //         if($numberValidationResult['total_valid'] === 0) 
    //         {
    //             Log::error('❌ No valid numbers found', [
    //                 'all_numbers_sample' => array_slice($allNumbers, 0, 10),
    //                 'invalid_numbers_sample' => array_slice($numberValidationResult['invalid'], 0, 10)
    //             ]);
    //             return [
    //                 'status' => false, 
    //                 'message' => 'No valid Indian phone numbers found. Please check your input.'
    //             ];
    //         }

    //         // Now validate messages for valid numbers only
    //         Log::info('📝 Starting message and template validation');
    //         $validMessages = [];
    //         $processedNumbers = []; // Track processed numbers to avoid duplicates
    //         $totalCreditCost = 0;
    //         $templateValidationErrors = [];
    //         $duplicateCount = 0;
            
    //         $startTime = microtime(true);
            
    //         foreach($messagesFromRequest as $index => $msg) 
    //         {
    //             // Clean and normalize the number
    //             $cleanedNumber = preg_replace('/[^0-9]/', '', trim($msg['number']));
    //             $normalizedNumber = self::normalizeDynamicPhoneNumber($cleanedNumber);
                
    //             // Check if this is a valid number and not already processed
    //             if(in_array($normalizedNumber, $numberValidationResult['valid'])) 
    //             {
    //                 if(in_array($normalizedNumber, $processedNumbers)) 
    //                 {
    //                     $duplicateCount++;
    //                     continue;
    //                 }
                    
    //                 Log::debug("✅ Validating template for number: $normalizedNumber", [
    //                     'index' => $index,
    //                     'normalized_number' => $normalizedNumber,
    //                     'message_length' => strlen($msg['message'])
    //                 ]);

    //                 // Validate template for each message
    //                 try {
    //                     $validationResult = self::validateStrictTemplate($template_data, $msg['message']);
                        
    //                     if($validationResult['status'] === false) 
    //                     {
    //                         Log::warning("⚠️ Template validation failed", [
    //                             'number' => $normalizedNumber,
    //                             'error' => $validationResult['message'],
    //                             'template_data' => substr($template_data, 0, 100),
    //                             'message' => substr($msg['message'], 0, 100)
    //                         ]);
    //                         $templateValidationErrors[] = [
    //                             'number' => $normalizedNumber,
    //                             'error' => $validationResult['message']
    //                         ];
    //                         continue;
    //                     }
    //                 } catch (\Exception $e) {
    //                     Log::error("🔥 Exception in template validation", [
    //                         'number' => $normalizedNumber,
    //                         'exception' => $e->getMessage(),
    //                         'trace' => $e->getTraceAsString()
    //                     ]);
    //                     $templateValidationErrors[] = [
    //                         'number' => $normalizedNumber,
    //                         'error' => 'Template validation exception: ' . $e->getMessage()
    //                     ];
    //                     continue;
    //                 }

    //                 // Calculate credit cost for this message
    //                 $charCount = mb_strlen($msg['message']);
    //                 $isUnicode = preg_match('/[^\x00-\x7F]/', $msg['message']);
    //                 $limit = $isUnicode ? 70 : 160;
    //                 $msgCredit = ceil($charCount / $limit);
                    
    //                 $validMessages[] = [
    //                     'number' => $normalizedNumber,
    //                     'message' => $msg['message'],
    //                     'credit' => $msgCredit
    //                 ];
                    
    //                 $totalCreditCost += $msgCredit;
    //                 $processedNumbers[] = $normalizedNumber;
                    
    //                 // Log progress for large datasets
    //                 if (count($validMessages) % 1000 === 0) {
    //                     Log::info("📈 Validation progress", [
    //                         'valid_messages_so_far' => count($validMessages),
    //                         'processed_numbers_so_far' => count($processedNumbers),
    //                         'current_memory' => round(memory_get_usage(true) / 1024 / 1024, 2) . 'MB'
    //                     ]);
    //                 }
    //             }
    //         }
            
    //         $validationTime = round(microtime(true) - $startTime, 2);
            
    //         Log::info('✅ Message validation completed', [
    //             'validation_time_seconds' => $validationTime,
    //             'total_valid_messages' => count($validMessages),
    //             'duplicates_removed' => $duplicateCount,
    //             'template_validation_errors' => count($templateValidationErrors),
    //             'total_credit_cost' => $totalCreditCost,
    //             'sample_template_errors' => array_slice($templateValidationErrors, 0, 3)
    //         ]);

    //         // Check if we have any valid messages
    //         if(count($validMessages) === 0) 
    //         {
    //             Log::error('❌ No valid messages found after validation', [
    //                 'template_errors_count' => count($templateValidationErrors),
    //                 'sample_errors' => array_slice($templateValidationErrors, 0, 5),
    //                 'duplicate_count' => $duplicateCount
    //             ]);
    //             return [
    //                 'status' => false,
    //                 'message' => 'No valid messages found after validation.',
    //                 'details' => [
    //                     'template_errors' => $templateValidationErrors,
    //                     'duplicates_removed' => $duplicateCount
    //                 ]
    //             ];
    //         }

    //         // Check balance
    //         Log::info('💰 Checking credit balance', [
    //             'required_credit' => $totalCreditCost,
    //             'available_credit' => $balance_credit,
    //             'difference' => $balance_credit - $totalCreditCost
    //         ]);

    //         if($totalCreditCost > $balance_credit) 
    //         {
    //             Log::error('❌ Insufficient balance', [
    //                 'required' => $totalCreditCost,
    //                 'available' => $balance_credit,
    //                 'deficit' => $totalCreditCost - $balance_credit
    //             ]);
    //             return ['status' => false, 'message' => 'Insufficient Balance'];
    //         }

    //         // Create message record
    //         Log::info('💾 Creating message record in database');
    //         try {
    //             $jobId = generateRandomAlphaNumeric(15);
    //             Log::debug('📝 Generated Job ID', ['job_id' => $jobId]);
                
    //             $message = self::create([
    //                 'route' => $request->route_name,
    //                 'campaign_name' => $request->campaign_name,
    //                 'senderid' => $request->senderid,
    //                 'template_id' => $request->template_ID,
    //                 'message' => $template_data,
    //                 'unicode_type' => $request->unicode_type,
    //                 'original_url' => $request->original_url,
    //                 'job_id' => $jobId,
    //                 'form_type' => 'Dynamic SMS',
    //                 'senderid_name' => $senderid_name,
    //                 'pe_id' => $pe_id,
    //                 'ip_address' => $request->ip(),
    //                 'userid' => $user->id,
    //                 'service_id' => $serviceIdValue,
    //                 'url_status' => $url_status,
    //                 'msg_credit' => $totalCreditCost,
    //                 'numbers_count' => count($validMessages),
    //                 'created_by' => $user->id,
    //                 'created_at' => now(),
    //                 'updated_at' => null,
    //             ]);
                
    //             Log::info('✅ Message record created', [
    //                 'message_id' => $message->id,
    //                 'job_id' => $message->job_id,
    //                 'numbers_count' => $message->numbers_count
    //             ]);
    //         } catch (\Exception $e) {
    //             Log::error('🔥 Database error creating message record', [
    //                 'error' => $e->getMessage(),
    //                 'trace' => $e->getTraceAsString(),
    //                 'data_attempted' => [
    //                     'campaign_name' => $request->campaign_name,
    //                     'template_id' => $request->template_ID,
    //                     'numbers_count' => count($validMessages)
    //                 ]
    //             ]);
    //             return [
    //                 'status' => false,
    //                 'message' => 'Database error creating campaign. Please try again.'
    //             ];
    //         }

    //         // Update credit balance
    //         Log::info('💳 Updating credit balance');
    //         try {
    //             $newBalance = $balance_credit - $totalCreditCost;
    //             $creditManageDetail->update(['balance' => $newBalance]);
    //             Log::debug('✅ Credit balance updated', [
    //                 'old_balance' => $balance_credit,
    //                 'new_balance' => $newBalance,
    //                 'deducted' => $totalCreditCost
    //             ]);
    //         } catch (\Exception $e) {
    //             Log::error('🔥 Error updating credit balance', [
    //                 'error' => $e->getMessage(),
    //                 'balance_details' => [
    //                     'user_id' => $user->id,
    //                     'route_id' => $route_id,
    //                     'old_balance' => $balance_credit,
    //                     'deduction' => $totalCreditCost
    //                 ]
    //             ]);
    //             // Continue anyway as the message is already created
    //         }

    //         // Create transaction log
    //         Log::info('📊 Creating transaction log');
    //         try {
    //             CampaignTransactionLog::create([
    //                 'job_id' => $message->job_id,
    //                 'campaign_name' => $request->campaign_name,
    //                 'total_numbers' => count($validMessages),
    //                 'total_credit' => $totalCreditCost,
    //                 'previous_balance' => $balance_credit,
    //                 'new_balance' => $newBalance,
    //                 'created_by' => $user->id,
    //                 'created_at' => now(),
    //                 'updated_at' => null,
    //             ]);
    //             Log::debug('✅ Transaction log created');
    //         } catch (\Exception $e) {
    //             Log::warning('⚠️ Error creating transaction log', [
    //                 'error' => $e->getMessage(),
    //                 'job_id' => $message->job_id
    //             ]);
    //             // Continue anyway
    //         }

    //         // Prepare messages for dispatch (without credit field)
    //         Log::info('📦 Preparing messages for job dispatch', [
    //             'total_messages_to_dispatch' => count($validMessages)
    //         ]);
            
    //         $messagesForDispatch = array_map(function($msg) {
    //             return [
    //                 'number' => $msg['number'],
    //                 'message' => $msg['message']
    //             ];
    //         }, $validMessages);

    //         // Dispatch jobs (let the jobs handle internal chunking)
    //         Log::info('🚀 Dispatching jobs to queue');
    //         try {
    //             dispatch(new SendDynamicUrlTrackingKeyJob($messagesForDispatch, $message, $user->id, $sender_ID));
    //             dispatch(new SendDynamicNumbersJob($messagesForDispatch, $message, $user->id, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));
                
    //             Log::info('✅ Jobs dispatched successfully', [
    //                 'job_id' => $message->job_id,
    //                 'queue_driver' => config('queue.default'),
    //                 'messages_dispatched' => count($messagesForDispatch)
    //             ]);
    //         } catch (\Exception $e) {
    //             Log::error('🔥 Error dispatching jobs', [
    //                 'error' => $e->getMessage(),
    //                 'trace' => $e->getTraceAsString(),
    //                 'queue_config' => config('queue')
    //             ]);
    //             // Even if job dispatch fails, the campaign is created
    //         }

    //         Log::info('🎉 Dynamic SMS processing COMPLETED SUCCESSFULLY', [
    //             'total_processing_time' => round(microtime(true) - LARAVEL_START, 2) . 's',
    //             'peak_memory_usage' => round(memory_get_peak_usage(true) / 1024 / 1024, 2) . 'MB',
    //             'user_id' => $user->id,
    //             'job_id' => $message->job_id,
    //             'summary' => [
    //                 'total_received' => count($messagesFromRequest),
    //                 'valid_messages' => count($validMessages),
    //                 'invalid_numbers' => $numberValidationResult['total_invalid'],
    //                 'duplicates_removed' => $duplicateCount,
    //                 'template_errors' => count($templateValidationErrors),
    //                 'total_credit_used' => $totalCreditCost,
    //                 'new_balance' => $newBalance
    //             ]
    //         ]);

    //         return [
    //             'status' => true,
    //             'message' => 'Dynamic SMS campaign created successfully',
    //             'data' => [
    //                 'job_id' => $message->job_id,
    //                 'total_received' => count($messagesFromRequest),
    //                 'valid_messages' => count($validMessages),
    //                 'invalid_numbers' => $numberValidationResult['total_invalid'],
    //                 'duplicates_removed' => $duplicateCount,
    //                 'template_errors' => count($templateValidationErrors),
    //                 'total_credit_used' => $totalCreditCost,
    //                 'new_balance' => $newBalance
    //             ]
    //         ];
    //     }
    // }
    public static function createAndProcess($request, $user, $backgroundMode = false, $jobId = null)
    {
        // >>>>> FIX 1: Determine if this is bulk SMS or dynamic SMS based on request data, not Route::currentRouteName() <<<<<
        $isBulkSms = true; // Default to bulk SMS
        $isDynamicSms = false;
        
        // Check if it's dynamic SMS by looking for messages array or form_type
        if (isset($request->messages) && is_array($request->messages)) {
            $isBulkSms = false;
            $isDynamicSms = true;
        }
        
        // Alternatively, check form_type if provided
        if (isset($request->form_type)) {
            if ($request->form_type === 'Dynamic SMS') {
                $isBulkSms = false;
                $isDynamicSms = true;
            } elseif ($request->form_type === 'Bulk SMS') {
                $isBulkSms = true;
                $isDynamicSms = false;
            }
        }

        // Fetch senderid_name from sender_details table
        $senderDetail = SenderDetail::where('id', $request->senderid)->first();
        $senderid_name = $senderDetail ? $senderDetail->sender_ID : null;

        // Fetch pe_id from sender_details table
        $pe_id = $senderDetail ? $senderDetail->principle_entity_ID : null;

        // Fetch sender_ID from sender_details table
        $sender_ID = $senderDetail ? $senderDetail->sender_ID : null;

        // Fetch template_ID from template_details table
        //$templateDetail = TemplateDetail::where('template_ID', $request->template_ID)->first();
        $templateDetail = TemplateDetail::where('template_ID', $request->template_ID)
            ->where('user_ID', $user->id)
            ->where('isActive', 1)
            ->where('isDeleted', 0)
            ->whereRaw('JSON_CONTAINS(sender_ID, ?)', [(string)(int)$request->senderid])
            ->first();
        $template_data = $templateDetail ? $templateDetail->template_data : null;
        $content_type_ID = $templateDetail ? $templateDetail->content_type_ID : null;

        // Fetch start time and end time from selected route name
        $routeDetail = ManageRoute::where('route_name', $request->route_name)->first();

        if(!$routeDetail) 
        {
            return ['status' => false, 'message' => 'Selected route not found.'];
        }

        $route_id = $routeDetail->id;
        $DND_check_ID = $routeDetail->DND_check_ID;
        $start_time = $routeDetail->start_time;
        $end_time = $routeDetail->end_time;

        $currentTime = Carbon::now();

        try 
        {
            $startTime = Carbon::parse($start_time)->setDate($currentTime->year, $currentTime->month, $currentTime->day);
            $endTime = Carbon::parse($end_time)->setDate($currentTime->year, $currentTime->month, $currentTime->day);
        } 
        catch(\Exception $e) 
        {
            return ['status' => false, 'message' => 'Invalid time format for route start or end time.'];
        }

        if(!$currentTime->between($startTime, $endTime)) 
        {
            return ['status' => false, 'message' => 'Can not send message for selected route.'];
        }

        // Fetch sender block details
        $senderBlockDetail  = SenderBlock::where('sender_ID', $sender_ID)
                            ->where('user_ID', $user->id)
                            ->where('isActive', 1)
                            ->first();

        if($senderBlockDetail) 
        {
            return ['status' => false, 'message' => 'Sender ID has been blocked. You cannot send message with this Sender ID.'];
        }

        // Fetch sender routing details
        $senderRoutingDetail    = SenderRouting::where('sender_ID', $request->senderid)
                                ->where('user_ID', $user->id)
                                ->where('isActive', 1)
                                ->first();

        $gatewayNames = [];
        $gatewayDistribution = [];

        if(!$senderRoutingDetail) 
        {
            $planAssignDetail   = PlanAssign::where('user_ID', $user->id)
                                ->where('isActive', 1)
                                ->first();

            if($planAssignDetail)
            {
                $plan_ID = $planAssignDetail->plan_ID;

                $routingPlanDetail  = RoutingPlan::where('plan_ID', $plan_ID)
                                    ->where('route_ID', $route_id)
                                    ->where('isActive', 1)
                                    ->first();

                if($routingPlanDetail)
                {
                    $gateway_ID = $routingPlanDetail->gateway_ID;
                    $gatewayPercent = $routingPlanDetail->gateway_percent;

                    if(is_array($gateway_ID)) 
                    {
                        $smsGateways = SmsGateway::whereIn('id', $gateway_ID)->get(['id', 'gateway_name']);
                        
                        foreach($smsGateways as $index => $gateway) 
                        {
                            $gatewayNames[] = $gateway->gateway_name;

                            $gatewayDistribution[] = [
                                'id' => $gateway->id,
                                'name' => $gateway->gateway_name,
                                'percent' => isset($gatewayPercent[$index]) ? $gatewayPercent[$index] : 0
                            ];
                        }
                    } 
                    else 
                    {
                        $smsGateway = SmsGateway::find($gateway_ID);
                        
                        if($smsGateway) 
                        {
                            $gatewayNames[] = $smsGateway->gateway_name;

                            $gatewayDistribution[] = [
                                'id' => $smsGateway->id,
                                'name' => $smsGateway->gateway_name,
                                'percent' => 100
                            ];
                        }
                    }

                    $serviceIdValue = implode(',', $gatewayNames);
                }
                else
                {
                    return ['status' => false, 'message' => 'Incorrect route selection!'];
                }
            }
            else
            {
                return ['status' => false, 'message' => 'Incorrect route selection!'];
            }
        }
        else
        {
            $gateway_ID = $senderRoutingDetail->gateway_ID;
        
            $smsGateway = SmsGateway::find($gateway_ID);

            if($smsGateway) 
            {
                $gatewayNames[] = $smsGateway->gateway_name;

                $gatewayDistribution[] = [
                    'id' => $smsGateway->id,
                    'name' => $smsGateway->gateway_name,
                    'percent' => 100
                ];
            }

            $serviceIdValue = $smsGateway ? $smsGateway->gateway_name : '';
        }

        // Guard: if no gateway was resolved (sender routing found but gateway row missing),
        // fail fast here rather than dispatching SendNumbersJob with empty gatewayDistribution
        // which would crash with an uncaught TypeError inside prepareGatewayIterator().
        if (empty($gatewayDistribution)) {
            return ['status' => false, 'message' => 'No valid SMS gateway found for the selected route. Please check your routing configuration.'];
        }

        // Credit Management
        $creditManageDetail = CreditManage::where('route_id', $route_id)
                            ->where('userid', $user->id)
                            ->where('isActive', 1)
                            ->first();

        if($creditManageDetail)
        {
            $balance_credit = $creditManageDetail->balance;
        }
        else
        {
            return ['status' => false, 'message' => 'No credit balance found for this route.'];
        }

        // Calculate estimated message credit cost
        $charCount = mb_strlen($request->message);
        $isUnicode = preg_match('/[^\x00-\x7F]/', $request->message);
        $limit = $isUnicode ? 70 : 160;
        $msgCredit = ceil($charCount / $limit);
        
        // >>>>> FIX 2: Use the determined route type instead of Route::currentRouteName() <<<<<
        if ($isBulkSms) 
        {
            // >>>>> CHANGE 1: For immediate response, return early with basic validation <<<<<
            if (!$backgroundMode) {
                // Validate template format synchronously before dispatching job
                if ($template_data && !empty(trim((string)$template_data))) {
                    $templateValidation = self::validateStrictTemplate((string)$template_data, (string)($request->message ?? ''));
                    if ($templateValidation['status'] === false) {
                        return $templateValidation;
                    }
                }
                // Perform quick validation and return immediate response
                $quickValidation = self::quickValidateForImmediateResponse($request, $user, $route_id, $balance_credit, $msgCredit);
                
                if ($quickValidation['status'] === false) {
                    return $quickValidation;
                }
                
                // Generate job ID immediately
                $immediateJobId = $jobId ?: generateRandomAlphaNumeric(15);
                
                // Return success with job ID for background processing
                return [
                    'status' => true,
                    'immediate_response' => true,
                    'job_id' => $immediateJobId,
                    'estimated_count' => $quickValidation['estimated_count'],
                    'message' => 'Message created successfully.'
                ];
            }
            // >>>>> END CHANGE 1 <<<<<
            
            // Validate and extract numbers based on method (this is the time-consuming part)
            $numberValidationResult = self::extractAndValidateNumbers($request, $request->method);
            
            // Check if we have any valid numbers
            if($numberValidationResult['total_valid'] === 0) 
            {
                return [
                    'status' => false, 
                    'message' => 'No valid Indian phone numbers found. Please check your input.'
                ];
            }

            // Use only valid numbers count for credit calculation
            $validNumberCount = $numberValidationResult['total_valid'];
            $totalCreditCost = $msgCredit * $validNumberCount;
            
            // Check if balance is sufficient
            if($totalCreditCost > $balance_credit) 
            {
                return ['status' => false, 'message' => 'Insufficient Balance'];
            }

            // URL Status
            $url_status = !empty($request->original_url) ? 1 : 0;

            // Template message validation
            $validationResult = self::validateStrictTemplate($template_data, $request->message);
            
            if($validationResult['status'] === false) 
            {
                return $validationResult;
            }

            // Use provided jobId or generate new one
            $finalJobId = $jobId ?: generateRandomAlphaNumeric(15);
            
            $message = self::create([
                'route' => $request->route_name,
                'method' => $request->method,
                'campaign_name' => $request->campaign_name,
                'senderid' => $request->senderid,
                'template_id' => $request->template_ID,
                'message' => $template_data,
                'unicode_type' => $request->unicode_type,
                'original_url' => $request->original_url,
                'job_id' => $finalJobId,
                'form_type' => 'Bulk SMS',
                'senderid_name' => $senderid_name,
                'pe_id' => $pe_id,
                'ip_address' => $request->ip(),
                'userid' => $user->id,
                'service_id' => $serviceIdValue,
                'url_status' => $url_status,
                'msg_credit' => $totalCreditCost,
                'numbers_count' => $validNumberCount,
                'created_by' => $user->id,
                'created_at' => now(),
                'updated_at' => null,
            ]);

            // Deduct credit from balance
            $newBalance = $balance_credit - $totalCreditCost;
            $creditManageDetail->update(['balance' => $newBalance]);
            
            // Create transaction log
            CampaignTransactionLog::create([
                'job_id' => $finalJobId,
                'campaign_name' => $request->campaign_name,
                'total_numbers' => $validNumberCount,
                'total_credit' => $totalCreditCost,
                'previous_balance' => $balance_credit,
                'new_balance' => $newBalance,
                'created_by' => $user->id,
                'created_at' => now(),
                'updated_at' => null,
            ]);

            // Use the pre-validated numbers from numberValidationResult
            $validNumbers = $numberValidationResult['valid'];
            
            // Remove duplicate mobile numbers (additional safety)
            Log::info('DEDUP START', ['count' => count($validNumbers)]);
            $validNumbers = self::removeDuplicateNumbers($validNumbers);
            Log::info('DEDUP END', ['count' => count($validNumbers)]);

            // Process in chunks for better performance
            $chunkSize = 5000; // Process 5000 numbers at a time
            $chunks = array_chunk($validNumbers, $chunkSize);
            
            foreach ($chunks as $chunkIndex => $chunk) {
                
                dispatch(new SendUrlTrackingKeyJob($chunk, $message, $user->id, $sender_ID));
                dispatch(new SendNumbersJob($chunk, $message, $user->id, $request->message, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));
            }

            return [
                'status' => true,
                'message' => 'Bulk SMS processed successfully in background.',
                'job_id' => $finalJobId,
                'numbers_count' => count($validNumbers),
                'chunks_count' => count($chunks)
            ];
        }
        // >>>>> FIX 3: Handle dynamic SMS without Route::currentRouteName() <<<<<
        else if ($isDynamicSms)
        {
            Log::info('Processing dynamic SMS', ['background_mode' => $backgroundMode]);

            $messagesFromRequest = $request->input('messages');

            // Fallback: if messages not in request but preview_id is present, load from cache directly
            if ((!is_array($messagesFromRequest) || empty($messagesFromRequest)) && $request->filled('preview_id')) {
                $fallbackCacheKey = "dynamic_preview_{$request->input('preview_id')}";
                $messagesFromRequest = Cache::get($fallbackCacheKey);
                Log::info('Loaded messages from cache via preview_id fallback', [
                    'preview_id' => $request->input('preview_id'),
                    'messages_count' => is_array($messagesFromRequest) ? count($messagesFromRequest) : 'NOT_ARRAY'
                ]);
            }

            Log::debug('Messages received from request', [
                'messages_count' => is_array($messagesFromRequest) ? count($messagesFromRequest) : 'NOT_ARRAY',
                'sample_first_3' => is_array($messagesFromRequest) ? array_slice($messagesFromRequest, 0, 3) : $messagesFromRequest
            ]);

            if(!is_array($messagesFromRequest) || empty($messagesFromRequest))
            {
                Log::error(' Messages array is empty or not an array', [
                    'type' => gettype($messagesFromRequest),
                    'value' => $messagesFromRequest
                ]);
                return [
                    'status' => false,
                    'message' => '',
                    'errors' => ['messages' => ['Messages array is required and must be a valid array.']]
                ];
            }

            // >>>>> CHANGE: For immediate response, return early with basic validation <<<<<
            if (!$backgroundMode) {
                // Perform quick validation and return immediate response
                $quickValidation = self::quickValidateDynamicForImmediateResponse($request, $user, $route_id, $balance_credit, $messagesFromRequest);

                if ($quickValidation['status'] === false) {
                    return $quickValidation;
                }

                // Generate job ID immediately
                $immediateJobId = $jobId ?: generateRandomAlphaNumeric(15);

                // Return success with job ID for background processing
                return [
                    'status' => true,
                    'immediate_response' => true,
                    'job_id' => $immediateJobId,
                    'estimated_count' => $quickValidation['estimated_count'],
                    'message' => 'Message created successfully.'
                ];
            }
            // >>>>> END CHANGE <<<<<

            // Check if template data is valid
            Log::debug(' Checking template data', [
                'template_data_exists' => isset($template_data),
                'template_data_type' => gettype($template_data),
                'template_data_length' => $template_data ? strlen($template_data) : 0,
                'template_data_sample' => $template_data ? substr($template_data, 0, 100) : 'NULL'
            ]);

            if (!$template_data || empty(trim($template_data))) {
                Log::error('Template data is invalid or empty', [
                    'template_ID_from_request' => $request->template_ID,
                    'template_detail_found' => isset($templateDetail) ? 'YES' : 'NO'
                ]);
                return [
                    'status' => false,
                    'message' => 'Invalid template or template not found.'
                ];
            }

            $allNumbers = [];
            Log::info('Starting message validation', ['total_messages' => count($messagesFromRequest)]);

            // OPTIMIZATION: Skip detailed per-message logging for large datasets
            $skipDetailedLogging = $backgroundMode && count($messagesFromRequest) > 1000;

            foreach ($messagesFromRequest as $index => $msg)
            {
                if (!isset($msg['message']))
                {
                    if (!$skipDetailedLogging) {
                        Log::error("Message content missing", ['index' => $index]);
                    }
                    return [
                        'status' => false,
                        'message' => 'Message content missing for entry '.$index,
                        'errors' => ['messages.' . $index . '.message' => ['Message content is required.']]
                    ];
                }

                if (!isset($msg['number']))
                {
                    if (!$skipDetailedLogging) {
                        Log::error("Phone number missing", ['index' => $index]);
                    }
                    return [
                        'status' => false,
                        'message' => 'Phone number missing for entry '.$index,
                        'errors' => ['messages.' . $index . '.number' => ['Phone number is required.']]
                    ];
                }

                $allNumbers[] = $msg['number'];

                // OPTIMIZATION: Log progress much less frequently (every 25k instead of 5k)
                if (!$skipDetailedLogging && count($messagesFromRequest) > 10000 && ($index + 1) % 25000 === 0) {
                    Log::info("Processing progress", [
                        'processed' => $index + 1,
                        'total' => count($messagesFromRequest),
                        'percentage' => round(($index + 1) / count($messagesFromRequest) * 100, 1) . '%'
                    ]);
                }
            }

            Log::info('Basic validation completed', [
                'total_numbers_collected' => count($allNumbers)
            ]);

            // First validate all numbers
            Log::info('Starting number validation');
            $numberValidationResult = self::extractAndValidateDynamicNumbers($allNumbers);

            // Free memory immediately
            unset($allNumbers);

            Log::info('Number validation results', [
                'total_received' => count($messagesFromRequest),
                'valid_numbers' => $numberValidationResult['total_valid'],
                'invalid_numbers' => $numberValidationResult['total_invalid']
            ]);

            if($numberValidationResult['total_valid'] === 0) 
            {
                Log::error('No valid numbers found', [
                    'all_numbers_sample' => array_slice($allNumbers, 0, 10),
                    'invalid_numbers_sample' => array_slice($numberValidationResult['invalid'], 0, 10)
                ]);
                return [
                    'status' => false, 
                    'message' => 'No valid Indian phone numbers found. Please check your input.'
                ];
            }

            // Now validate messages for valid numbers only
            Log::info('Starting message and template validation');
            $validMessages = [];
            $totalCreditCost = 0;
            $templateValidationErrors = [];
            $duplicateCount = 0;

            $startTime = microtime(true);

            // OPTIMIZATION: Convert valid numbers array to hash map for O(1) lookup instead of O(n)
            // This is CRITICAL for performance with large datasets (150k messages)
            $validNumbersMap = array_flip($numberValidationResult['valid']);

            // Free the original array to save memory
            unset($numberValidationResult['valid']);

            // OPTIMIZATION: Use hash map for duplicate tracking as well
            $processedNumbersMap = [];

            // OPTIMIZATION: In background mode, skip expensive per-message template validation
            // The frontend already validated 100 sample messages, so we can trust the rest
            // Also skip when using preview_id (already validated during preview generation)
            $hasPreviewId = $request->filled('preview_id');
            $skipTemplateValidation = ($backgroundMode && count($messagesFromRequest) > 1000) || $hasPreviewId;

            if ($skipTemplateValidation) {
                Log::info('FAST MODE: Skipping per-message template validation', [
                    'reason' => $hasPreviewId ? 'using preview_id' : 'background processing',
                    'preview_id' => $hasPreviewId ? $request->input('preview_id') : null
                ]);
            }

            foreach($messagesFromRequest as $index => $msg)
            {
                // Clean and normalize the number
                $cleanedNumber = preg_replace('/[^0-9]/', '', trim($msg['number']));
                $normalizedNumber = self::normalizeDynamicPhoneNumber($cleanedNumber);

                // OPTIMIZATION: Check if this is a valid number using isset() - O(1) instead of in_array() - O(n)
                if(isset($validNumbersMap[$normalizedNumber]))
                {
                    // OPTIMIZATION: Check duplicates using isset() - O(1) instead of in_array() - O(n)
                    if(isset($processedNumbersMap[$normalizedNumber]))
                    {
                        $duplicateCount++;
                        continue;
                    }

                    $processedNumbersMap[$normalizedNumber] = true;

                    // OPTIMIZATION: Skip template validation in fast mode (background with large datasets)
                    if (!$skipTemplateValidation) {
                        // Validate template for each message (only when needed)
                        try {
                            $validationResult = self::validateStrictTemplate($template_data, $msg['message']);

                            if($validationResult['status'] === false)
                            {
                                $templateValidationErrors[] = [
                                    'number' => $normalizedNumber,
                                    'error' => $validationResult['message']
                                ];
                                continue;
                            }
                        } catch (\Exception $e) {
                            $templateValidationErrors[] = [
                                'number' => $normalizedNumber,
                                'error' => 'Template validation exception: ' . $e->getMessage()
                            ];
                            continue;
                        }
                    }

                    // Calculate credit cost for this message
                    $charCount = mb_strlen($msg['message']);
                    $isUnicode = preg_match('/[^\x00-\x7F]/', $msg['message']);
                    $limit = $isUnicode ? 70 : 160;
                    $msgCredit = ceil($charCount / $limit);

                    $validMessages[] = [
                        'number' => $normalizedNumber,
                        'message' => $msg['message'],
                        'credit' => $msgCredit
                    ];

                    $totalCreditCost += $msgCredit;

                    // OPTIMIZATION: Log progress much less frequently (every 10k instead of 1k)
                    if (count($validMessages) % 10000 === 0) {
                        Log::info("Validation progress", [
                            'valid_messages_so_far' => count($validMessages),
                            'current_memory' => round(memory_get_usage(true) / 1024 / 1024, 2) . 'MB'
                        ]);
                    }
                }
            }

            // Free memory from hash maps
            unset($validNumbersMap, $processedNumbersMap);

            $validationTime = round(microtime(true) - $startTime, 2);

            // Store counts before freeing memory
            $templateErrorsCount = count($templateValidationErrors);

            Log::info('Message validation completed', [
                'validation_time_seconds' => $validationTime,
                'total_valid_messages' => count($validMessages),
                'duplicates_removed' => $duplicateCount,
                'template_validation_errors' => $templateErrorsCount,
                'total_credit_cost' => $totalCreditCost,
                'avg_time_per_message' => round($validationTime / count($messagesFromRequest), 4) . 's'
            ]);

            // Free memory from validation errors
            unset($templateValidationErrors);

            // Check if we have any valid messages
            if(count($validMessages) === 0) 
            {
                Log::error('No valid messages found after validation', [
                    'template_errors_count' => count($templateValidationErrors),
                    'sample_errors' => array_slice($templateValidationErrors, 0, 5),
                    'duplicate_count' => $duplicateCount
                ]);
                return [
                    'status' => false,
                    'message' => 'No valid messages found after validation.',
                    'details' => [
                        'template_errors' => $templateValidationErrors,
                        'duplicates_removed' => $duplicateCount
                    ]
                ];
            }

            // Check balance
            Log::info('Checking credit balance', [
                'required_credit' => $totalCreditCost,
                'available_credit' => $balance_credit,
                'difference' => $balance_credit - $totalCreditCost
            ]);

            if($totalCreditCost > $balance_credit)
            {
                Log::error('Insufficient balance', [
                    'required' => $totalCreditCost,
                    'available' => $balance_credit,
                    'deficit' => $totalCreditCost - $balance_credit
                ]);
                return ['status' => false, 'message' => 'Insufficient Balance'];
            }

            // URL Status
            $url_status = !empty($request->original_url) ? 1 : 0;

            // Create message record
            $jobId = generateRandomAlphaNumeric(15);

            try {
                $message = self::create([
                    'route' => $request->route_name,
                    'campaign_name' => $request->campaign_name,
                    'senderid' => $request->senderid,
                    'template_id' => $request->template_ID,
                    'message' => $template_data,
                    'unicode_type' => $request->unicode_type,
                    'original_url' => $request->original_url,
                    'job_id' => $jobId,
                    'form_type' => 'Dynamic SMS',
                    'senderid_name' => $senderid_name,
                    'pe_id' => $pe_id,
                    'ip_address' => $request->ip(),
                    'userid' => $user->id,
                    'service_id' => $serviceIdValue,
                    'url_status' => $url_status,
                    'msg_credit' => $totalCreditCost,
                    'numbers_count' => 0, // Set to 0, will be incremented by SendDynamicNumbersJob
                    'created_by' => $user->id,
                    'created_at' => now(),
                    'updated_at' => null,
                ]);

                Log::info('Message record created', [
                    'message_id' => $message->id,
                    'job_id' => $message->job_id
                ]);
            } catch (\Exception $e) {
                Log::error('Database error creating message record', [
                    'error' => $e->getMessage(),
                    'trace' => $e->getTraceAsString(),
                    'data_attempted' => [
                        'campaign_name' => $request->campaign_name,
                        'template_id' => $request->template_ID,
                        'numbers_count' => count($validMessages)
                    ]
                ]);
                return [
                    'status' => false,
                    'message' => 'Database error creating campaign. Please try again.'
                ];
            }

            // Update credit balance
            try {
                $newBalance = $balance_credit - $totalCreditCost;
                $creditManageDetail->update(['balance' => $newBalance]);
            } catch (\Exception $e) {
                Log::error('Error updating credit balance', [
                    'error' => $e->getMessage(),
                    'user_id' => $user->id
                ]);
                // Continue anyway as the message is already created
            }

            // Create transaction log
            try {
                CampaignTransactionLog::create([
                    'job_id' => $message->job_id,
                    'campaign_name' => $request->campaign_name,
                    'total_numbers' => count($validMessages),
                    'total_credit' => $totalCreditCost,
                    'previous_balance' => $balance_credit,
                    'new_balance' => $newBalance,
                    'created_by' => $user->id,
                    'created_at' => now(),
                    'updated_at' => null,
                ]);
            } catch (\Exception $e) {
                Log::warning('Error creating transaction log', [
                    'error' => $e->getMessage(),
                    'job_id' => $message->job_id
                ]);
                // Continue anyway
            }

            // Prepare messages for dispatch (without credit field)
            Log::info('Preparing messages for job dispatch', [
                'total_messages_to_dispatch' => count($validMessages)
            ]);
            
            $messagesForDispatch = array_map(function($msg) {
                return [
                    'number' => $msg['number'],
                    'message' => $msg['message']
                ];
            }, $validMessages);

            // Dispatch jobs (let the jobs handle internal chunking)
            try {
                (new SendDynamicUrlTrackingKeyJob($messagesForDispatch, $message, $user->id, $sender_ID))->handle();
                (new SendDynamicNumbersJob($messagesForDispatch, $message, $user->id, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution))->handle();

                Log::info('Jobs executed successfully', [
                    'job_id' => $message->job_id,
                    'messages_processed' => count($messagesForDispatch)
                ]);
            } catch (\Exception $e) {
                Log::error('Error executing dynamic SMS jobs', [
                    'error' => $e->getMessage(),
                    'job_id' => $message->job_id,
                    'trace' => $e->getTraceAsString()
                ]);
                // Update campaign status so it doesn't appear as successful
                try {
                    $message->update([
                        'status' => 'Failed',
                        'error_reason' => 'Job Processing Error: ' . $e->getMessage(),
                    ]);
                } catch (\Throwable $updateEx) {
                    Log::warning('Failed to update message status after job error', [
                        'job_id' => $message->job_id,
                        'error' => $updateEx->getMessage()
                    ]);
                }
            }

            // Store values before freeing memory
            $totalReceived = count($messagesFromRequest);
            $totalValid = count($validMessages);
            $totalInvalid = $numberValidationResult['total_invalid'];

            Log::info('Dynamic SMS processing completed', [
                'job_id' => $message->job_id,
                'total_received' => $totalReceived,
                'valid_messages' => $totalValid,
                'peak_memory_mb' => round(memory_get_peak_usage(true) / 1024 / 1024, 2)
            ]);

            // Free memory
            unset($messagesForDispatch, $validMessages, $numberValidationResult, $messagesFromRequest);
            gc_collect_cycles();

            return [
                'status' => true,
                'message' => 'Dynamic SMS campaign created successfully',
                'data' => [
                    'job_id' => $message->job_id,
                    'total_received' => $totalReceived,
                    'valid_messages' => $totalValid,
                    'invalid_numbers' => $totalInvalid,
                    'duplicates_removed' => $duplicateCount,
                    'template_errors' => $templateErrorsCount,
                    'total_credit_used' => $totalCreditCost,
                    'new_balance' => $newBalance
                ],
                'numbers_count' => $totalValid
            ];
        
        }
}
    // >>>>> CHANGE 4: Add this new helper method <<<<<
    private static function quickValidateForImmediateResponse($request, $user, $route_id, $balance_credit, $msgCredit)
    {
        try {
            
            // Quick number count estimation (fast)
            $estimatedCount = 0;
            
            if($request['method'] === 'Numbers' && !empty($request['numbers'])) 
            {
                $numbers = preg_split('/[, \r\n]+/', trim($request->numbers), -1, PREG_SPLIT_NO_EMPTY);
                $estimatedCount = count($numbers);
            }
            elseif($request->method === 'Groups' && !empty($request->group_IDs)) 
            {
                // Quick estimate from groups
                $groupIds = $request->group_IDs;
                if (is_array($groupIds) && !empty($groupIds)) {
                    // Get actual count from database for accuracy
                    $estimatedCount = \App\Models\ContactDetails::whereIn('group_ID', $groupIds)
                        ->distinct('contact_no')
                        ->count();
                }
            }
            elseif($request->method === 'CSV' && $request->hasFile('csv_file')) 
            {
                // Quick file size estimate
                $fileSize = $request->file('csv_file')->getSize();
                $estimatedCount = min(100000, intval($fileSize / 50)); // Rough estimate: 50 bytes per number
            }
            
            if ($estimatedCount === 0) {
                return ['status' => false, 'message' => 'No numbers found to process.'];
            }
            
            // Quick credit estimate
            $estimatedCreditCost = $msgCredit * $estimatedCount;
            
            if ($estimatedCreditCost > $balance_credit) {
                return ['status' => false, 'message' => 'Insufficient Balance for estimated ' . $estimatedCount . ' numbers'];
            }
            
            return [
                'status' => true,
                'estimated_count' => $estimatedCount,
                'estimated_credit' => $estimatedCreditCost
            ];
            
        } catch (\Exception $e) {
            
            return ['status' => false, 'message' => 'Validation failed. Please try again.'];
        }
    }

    private static function quickValidateDynamicForImmediateResponse($request, $user, $route_id, $balance_credit, $messagesFromRequest)
    {
        try {
            // Quick validation for dynamic SMS
            if (!is_array($messagesFromRequest) || empty($messagesFromRequest)) {
                return ['status' => false, 'message' => 'Messages array is required and must be a valid array.'];
            }

            // Estimate count from messages array
            $estimatedCount = count($messagesFromRequest);

            if ($estimatedCount === 0) {
                return ['status' => false, 'message' => 'No messages found to process.'];
            }

            // Calculate average credit cost (assume unicode for safety)
            // For dynamic SMS, we'll use a rough estimate based on the first message
            $firstMessage = reset($messagesFromRequest);
            $sampleMessage = $firstMessage['message'] ?? '';
            $charCount = mb_strlen($sampleMessage);
            $isUnicode = preg_match('/[^\x00-\x7F]/', $sampleMessage);
            $limit = $isUnicode ? 70 : 160;
            $avgMsgCredit = max(1, ceil($charCount / $limit));

            // Quick credit estimate (use average credit * count)
            $estimatedCreditCost = $avgMsgCredit * $estimatedCount;

            if ($estimatedCreditCost > $balance_credit) {
                return ['status' => false, 'message' => 'Insufficient Balance for estimated ' . $estimatedCount . ' messages'];
            }

            return [
                'status' => true,
                'estimated_count' => $estimatedCount,
                'estimated_credit' => $estimatedCreditCost
            ];

        } catch (\Exception $e) {
            Log::error('Quick validation failed for dynamic SMS', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            return ['status' => false, 'message' => 'Validation failed. Please try again.'];
        }
    }

    public static function createAndProcessWithAuth(Request $request)
    {
        //  Authenticate user based on request method
        $user = self::authenticateUser($request);
        
        if(!$user) 
        {
            return [
                'status' => false,
                'message' => 'Invalid Username or API Key.'
            ];
        }

        //  Login the user
        Auth::login($user);

        //  Process message creation (your existing logic)
        return static::httpCreateAndProcess($request, $user);
    }

    public static function createAndProcessWithGroupAuth(Request $request)
    {
        //  Authenticate user based on request method
        $user = self::authenticateUser($request);
        
        if(!$user) 
        {
            return [
                'status' => false,
                'message' => 'Invalid Username or API Key.'
            ];
        }

        //  Login the user
        Auth::login($user);

        //  Process message creation (your existing logic)
        return static::httpCreateAndProcessGroup($request, $user);
    }

    private static function authenticateUser(Request $request): ?User
{
    if ($request->isMethod('get')) {
        $username = $request->get('username');
        $apiKey   = $request->get('api_key') ?? $request->get('api-key');
    } else {
        $username = $request->header('username') 
                 ?? $request->input('username');

        $apiKey   = $request->header('api_key')
                 ?? $request->header('api-key')
                 ?? $request->header('x-api-key')
                 ?? $request->input('api_key')
                 ?? $request->input('api-key');
    }

    if (!$username || !$apiKey) {
        return null;
    }

    return User::authenticateByApiKey(trim($username), trim($apiKey));
}

    public static function httpCreateAndProcess($request, $user)
    {
        // Fetch senderid_name from sender_details table
        $senderDetail = SenderDetail::where('sender_ID', $request->sender_ID)->first();
        $senderid_name = $request->sender_ID;
        $senderid = $senderDetail ? $senderDetail->id : null;

        $pe_id = $request->principle_entity_ID;

        $sender_ID = $request->sender_ID;

        //  Fetch template_ID from template_details table
        $templateDetail = TemplateDetail::where('template_ID', $request->template_ID)->first();
        $template_data = $templateDetail ? $templateDetail->template_data : null;
        $content_type_ID = $templateDetail ? $templateDetail->content_type_ID : null;

        //  Fetch start time and end time from selected route name
        $routeDetail = ManageRoute::where('route_name', $request->route_name)->first();

        if(!$routeDetail) 
        {
            return ['status' => false, 'message' => 'Selected route not found.'];
        }

        $route_id = $routeDetail->id;
        $DND_check_ID = $routeDetail->DND_check_ID;
        $start_time = $routeDetail->start_time;
        $end_time = $routeDetail->end_time;

        $currentTime = Carbon::now();

        try 
        {
            $startTime = Carbon::parse($start_time)->setDate($currentTime->year, $currentTime->month, $currentTime->day);
            $endTime = Carbon::parse($end_time)->setDate($currentTime->year, $currentTime->month, $currentTime->day);
        } 
        catch(\Exception $e) 
        {
            return ['status' => false, 'message' => 'Invalid time format for route start or end time.'];
        }

        if(!$currentTime->between($startTime, $endTime)) 
        {
            return ['status' => false, 'message' => 'Can not send message for selected route.'];
        }

        //  Fetch sender block details
        $senderBlockDetail  = SenderBlock::where('sender_ID', $sender_ID)
                            ->where('user_ID', $user->id)
                            ->where('isActive', 1)
                            ->first();

        if($senderBlockDetail) 
        {
            return ['status' => false, 'message' => 'Sender ID has been blocked. You cannot send message with this Sender ID.'];
        }

        //  Fetch sender routing details
        $senderRoutingDetail    = SenderRouting::where('sender_ID', $senderid)
                                ->where('user_ID', $user->id)
                                ->where('isActive', 1)
                                ->first();

        $gatewayNames = [];
        $gatewayDistribution = [];

        if(!$senderRoutingDetail) 
        {
            $planAssignDetail   = PlanAssign::where('user_ID', $user->id)
                                ->where('isActive', 1)
                                ->first();

            if($planAssignDetail)
            {
                $plan_ID = $planAssignDetail->plan_ID;

                $routingPlanDetail  = RoutingPlan::where('plan_ID', $plan_ID)
                                    ->where('route_ID', $route_id)
                                    ->where('isActive', 1)
                                    ->first();

                if($routingPlanDetail)
                {
                    $gateway_ID = $routingPlanDetail->gateway_ID;
                    $gatewayPercent = $routingPlanDetail->gateway_percent;

                    if(is_array($gateway_ID)) 
                    {
                        $smsGateways = SmsGateway::whereIn('id', $gateway_ID)->get(['id', 'gateway_name']);
                        
                        foreach($smsGateways as $index => $gateway) 
                        {
                            $gatewayNames[] = $gateway->gateway_name;

                            $gatewayDistribution[] = [
                                'id' => $gateway->id,
                                'name' => $gateway->gateway_name,
                                'percent' => isset($gatewayPercent[$index]) ? $gatewayPercent[$index] : 0
                            ];
                        }
                    } 
                    else 
                    {
                        $smsGateway = SmsGateway::find($gateway_ID);
                        
                        if($smsGateway) 
                        {
                            $gatewayNames[] = $smsGateway->gateway_name;

                            $gatewayDistribution[] = [
                                'id' => $smsGateway->id,
                                'name' => $smsGateway->gateway_name,
                                'percent' => 100
                            ];
                        }
                    }

                    $serviceIdValue = implode(',', $gatewayNames);
                }
                else
                {
                    return ['status' => false, 'message' => 'Incorrect route selection!'];
                }
            }
            else
            {
                return ['status' => false, 'message' => 'Incorrect route selection!'];
            }
        }
        else
        {
            $gateway_ID = $senderRoutingDetail->gateway_ID;
        
            $smsGateway = SmsGateway::find($gateway_ID);

            if($smsGateway) 
            {
                $gatewayNames[] = $smsGateway->gateway_name;

                $gatewayDistribution[] = [
                    'id' => $smsGateway->id,
                    'name' => $smsGateway->gateway_name,
                    'percent' => 100
                ];
            }

            $serviceIdValue = $smsGateway ? $smsGateway->gateway_name : '';
        }

        //  Credit Management
        $creditManageDetail = CreditManage::where('route_id', $route_id)
                            ->where('userid', $user->id)
                            ->where('isActive', 1)
                            ->first();

        if($creditManageDetail)
        {
            $balance_credit = $creditManageDetail->balance;
        }
        else
        {
            return ['status' => false, 'message' => 'No credit balance found for this route.'];
        }
        
        // Validate and extract numbers based on method
        $numberValidationResult = self::extractAndValidateNumbers($request, 'Numbers');
        
        // Check if we have any valid numbers
        if($numberValidationResult['total_valid'] === 0) 
        {
            return [
                'status' => false, 
                'message' => 'No valid Indian phone numbers found. Please check your input.'
            ];
        }
        
        // Log invalid numbers (optional - for debugging)
        if($numberValidationResult['total_invalid'] > 0) 
        {
            // You can log these invalid numbers if needed
            Log::info('Invalid numbers detected', [
                'method' => $request->method,
                'invalid_count' => $numberValidationResult['total_invalid'],
                'invalid_numbers' => array_slice($numberValidationResult['invalid'], 0, 10)
            ]);
        }

        //  Calculate estimated message credit cost
        $charCount = mb_strlen($request->message);
        $isUnicode = preg_match('/[^\x00-\x7F]/', $request->message);
        $limit = $isUnicode ? 70 : 160;
        $msgCredit = ceil($charCount / $limit);
        
        //  Estimate number count based on method
        /* $estimatedNumberCount = 0;

        $numbersToProcess = preg_split('/[, \r\n]+/', trim($request->mobile_numbers), -1, PREG_SPLIT_NO_EMPTY);
        $numbersToProcess = array_map('trim', $numbersToProcess);
        $numbersToProcess = self::removeDuplicateNumbers($numbersToProcess);
        $estimatedNumberCount = count($numbersToProcess);
        
        $totalCreditCost = $msgCredit * $estimatedNumberCount; */
        
        // Use only valid numbers count for credit calculation
        $validNumberCount = $numberValidationResult['total_valid'];
        $totalCreditCost = $msgCredit * $validNumberCount;
        
        //  Check if balance is sufficient
        if($totalCreditCost > $balance_credit) 
        {
            return ['status' => false, 'message' => 'Insufficient Balance'];
        }

        //  URL Status
        $url_status = 0;

        //  Unicode Type
        if($request->msg_type == "text")
        {
            $unicode_type = 0;
        }
        else
        {
            $unicode_type = 1;
        }

        //  Template message validation
        $validationResult = self::validateStrictTemplate($template_data, $request->message);
        
        if($validationResult['status'] === false) 
        {
            return $validationResult;
        }

        $message = self::create([
            'route' => $request->route_name,
            'method' => 'API',
            'campaign_name' => $request->campaign_name,
            'senderid' => $senderid,
            'template_id' => $request->template_ID,
            'message' => $template_data,
            'unicode_type' => $unicode_type,
            'job_id' => generateRandomAlphaNumeric(15),
            'form_type' => 'HTTP SMS API',
            'senderid_name' => $senderid_name,
            'pe_id' => $pe_id,
            'ip_address' => $request->ip(),
            'userid' => $user->id,
            'service_id' => $serviceIdValue,
            'url_status' => $url_status,
            'msg_credit' => $totalCreditCost,
            'numbers_count' => $validNumberCount,
            'created_by' => $user->id,
            'created_at' => now(),
            'updated_at' => null,
        ]);

        //  Deduct credit from balance
        $newBalance = $balance_credit - $totalCreditCost;
        $creditManageDetail->update(['balance' => $newBalance]);
        
        //  Create transaction log
        CampaignTransactionLog::create([
            'job_id' => $message->job_id,
            'campaign_name' => $request->campaign_name,
            'total_numbers' => $validNumberCount,
            'total_credit' => $totalCreditCost,
            'previous_balance' => $balance_credit,
            'new_balance' => $newBalance,
            'created_by' => $user->id,
            'created_at' => now(),
            'updated_at' => null,
        ]);

        /* $numbersToProcess = [];

        $numbersToProcess = preg_split('/[, \r\n]+/', trim($request->mobile_numbers), -1, PREG_SPLIT_NO_EMPTY);
        $numbersToProcess = array_map('trim', $numbersToProcess); 
        
        //  Remove duplicate mobile numbers
        $numbersToProcess = self::removeDuplicateNumbers($numbersToProcess); */
        
        // Use the pre-validated numbers from numberValidationResult
        $validNumbers = $numberValidationResult['valid'];
        
        // Remove duplicate mobile numbers (additional safety)
        $validNumbers = self::removeDuplicateNumbers($validNumbers);

        // dispatch(new SendUrlTrackingKeyJob($validNumbers, $message, $user->id, $sender_ID));
        // dispatch(new SendNumbersJob($validNumbers, $message, $user->id, $request->message, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));

        SendUrlTrackingKeyJob::dispatchSync($validNumbers, $message, $user->id, $sender_ID); 
        SendNumbersJob::dispatchSync($validNumbers, $message, $user->id, $request->message, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution);

        return $message;
    }

    public static function httpCreateAndProcessGroup($request, $user)
    {
        // Fetch senderid_name from sender_details table
        $senderDetail = SenderDetail::where('sender_ID', $request->sender_ID)->first();
        $senderid_name = $request->sender_ID;
        $senderid = $senderDetail ? $senderDetail->id : null;

        $pe_id = $request->principle_entity_ID;

        $sender_ID = $request->sender_ID;

        //  Fetch template_ID from template_details table
        $templateDetail = TemplateDetail::where('template_ID', $request->template_ID)->first();
        $template_data = $templateDetail ? $templateDetail->template_data : null;
        $content_type_ID = $templateDetail ? $templateDetail->content_type_ID : null;

        //  Fetch start time and end time from selected route name
        $routeDetail = ManageRoute::where('route_name', $request->route_name)->first();

        if(!$routeDetail) 
        {
            return ['status' => false, 'message' => 'Selected route not found.'];
        }

        $route_id = $routeDetail->id;
        $DND_check_ID = $routeDetail->DND_check_ID;
        $start_time = $routeDetail->start_time;
        $end_time = $routeDetail->end_time;

        $currentTime = Carbon::now();

        try 
        {
            $startTime = Carbon::parse($start_time)->setDate($currentTime->year, $currentTime->month, $currentTime->day);
            $endTime = Carbon::parse($end_time)->setDate($currentTime->year, $currentTime->month, $currentTime->day);
        } 
        catch(\Exception $e) 
        {
            return ['status' => false, 'message' => 'Invalid time format for route start or end time.'];
        }

        if(!$currentTime->between($startTime, $endTime)) 
        {
            return ['status' => false, 'message' => 'Can not send message for selected route.'];
        }

        //  Fetch sender block details
        $senderBlockDetail  = SenderBlock::where('sender_ID', $sender_ID)
                            ->where('user_ID', $user->id)
                            ->where('isActive', 1)
                            ->first();

        if($senderBlockDetail) 
        {
            return ['status' => false, 'message' => 'Sender ID has been blocked. You cannot send message with this Sender ID.'];
        }

        //  Fetch sender routing details
        $senderRoutingDetail    = SenderRouting::where('sender_ID', $senderid)
                                ->where('user_ID', $user->id)
                                ->where('isActive', 1)
                                ->first();

        $gatewayNames = [];
        $gatewayDistribution = [];

        if(!$senderRoutingDetail) 
        {
            $planAssignDetail   = PlanAssign::where('user_ID', $user->id)
                                ->where('isActive', 1)
                                ->first();

            if($planAssignDetail)
            {
                $plan_ID = $planAssignDetail->plan_ID;

                $routingPlanDetail  = RoutingPlan::where('plan_ID', $plan_ID)
                                    ->where('route_ID', $route_id)
                                    ->where('isActive', 1)
                                    ->first();

                if($routingPlanDetail)
                {
                    $gateway_ID = $routingPlanDetail->gateway_ID;
                    $gatewayPercent = $routingPlanDetail->gateway_percent;

                    if(is_array($gateway_ID)) 
                    {
                        $smsGateways = SmsGateway::whereIn('id', $gateway_ID)->get(['id', 'gateway_name']);
                        
                        foreach($smsGateways as $index => $gateway) 
                        {
                            $gatewayNames[] = $gateway->gateway_name;

                            $gatewayDistribution[] = [
                                'id' => $gateway->id,
                                'name' => $gateway->gateway_name,
                                'percent' => isset($gatewayPercent[$index]) ? $gatewayPercent[$index] : 0
                            ];
                        }
                    } 
                    else 
                    {
                        $smsGateway = SmsGateway::find($gateway_ID);
                        
                        if($smsGateway) 
                        {
                            $gatewayNames[] = $smsGateway->gateway_name;

                            $gatewayDistribution[] = [
                                'id' => $smsGateway->id,
                                'name' => $smsGateway->gateway_name,
                                'percent' => 100
                            ];
                        }
                    }

                    $serviceIdValue = implode(',', $gatewayNames);
                }
                else
                {
                    return ['status' => false, 'message' => 'Incorrect route selection!'];
                }
            }
            else
            {
                return ['status' => false, 'message' => 'Incorrect route selection!'];
            }
        }
        else
        {
            $gateway_ID = $senderRoutingDetail->gateway_ID;
        
            $smsGateway = SmsGateway::find($gateway_ID);

            if($smsGateway) 
            {
                $gatewayNames[] = $smsGateway->gateway_name;

                $gatewayDistribution[] = [
                    'id' => $smsGateway->id,
                    'name' => $smsGateway->gateway_name,
                    'percent' => 100
                ];
            }

            $serviceIdValue = $smsGateway ? $smsGateway->gateway_name : '';
        }

        //  Credit Management
        $creditManageDetail = CreditManage::where('route_id', $route_id)
                            ->where('userid', $user->id)
                            ->where('isActive', 1)
                            ->first();

        if($creditManageDetail)
        {
            $balance_credit = $creditManageDetail->balance;
        }
        else
        {
            return ['status' => false, 'message' => 'No credit balance found for this route.'];
        }

        //  Calculate estimated message credit cost
        $charCount = mb_strlen($request->message);
        $isUnicode = preg_match('/[^\x00-\x7F]/', $request->message);
        $limit = $isUnicode ? 70 : 160;
        $msgCredit = ceil($charCount / $limit);
        
        //  Estimate number count based on method
        $estimatedNumberCount = 0;

        $groupNames = explode(',', $request->group_name);
        $groupNames = array_map('trim', $groupNames);

        $group_details = MST_group::where('group_name', $groupNames)->where('user_ID', $user->id)->where('isActive', 1)->get();

        $groupIds = $group_details->pluck('id')->toArray();

        $contacts = ContactDetails::whereIn('group_ID', $groupIds)->where('user_ID', $user->id)->pluck('contact_no')->toArray();
        $numbers = array_unique(array_map('trim', $contacts));
        
        $numbers = self::removeDuplicateNumbers($numbers);
        $estimatedNumberCount = count($numbers);
        
        $totalCreditCost = $msgCredit * $estimatedNumberCount;
        
        //  Check if balance is sufficient
        if($totalCreditCost > $balance_credit) 
        {
            return ['status' => false, 'message' => 'Insufficient Balance'];
        }

        //  URL Status
        $url_status = 0;

        //  Unicode Type
        if($request->msg_type == "text")
        {
            $unicode_type = 0;
        }
        else
        {
            $unicode_type = 1;
        }

        //  Template message validation
        $validationResult = self::validateStrictTemplate($template_data, $request->message);
        
        if($validationResult['status'] === false) 
        {
            return $validationResult;
        }

        $message = self::create([
            'route' => $request->route_name,
            'method' => 'API',
            'campaign_name' => $request->campaign_name,
            'senderid' => $senderid,
            'template_id' => $request->template_ID,
            'message' => $template_data,
            'unicode_type' => $unicode_type,
            'job_id' => generateRandomAlphaNumeric(15),
            'form_type' => 'HTTP SMS API',
            'senderid_name' => $senderid_name,
            'pe_id' => $pe_id,
            'ip_address' => $request->ip(),
            'userid' => $user->id,
            'service_id' => $serviceIdValue,
            'url_status' => $url_status,
            'msg_credit' => $totalCreditCost,
            'created_by' => $user->id,
            'created_at' => now(),
            'updated_at' => null,
        ]);

        //  Deduct credit from balance
        $newBalance = $balance_credit - $totalCreditCost;
        $creditManageDetail->update(['balance' => $newBalance]);
        
        //  Create transaction log
        CampaignTransactionLog::create([
            'job_id' => $message->job_id,
            'campaign_name' => $request->campaign_name,
            'total_numbers' => $estimatedNumberCount,
            'total_credit' => $totalCreditCost,
            'previous_balance' => $balance_credit,
            'new_balance' => $newBalance,
            'created_by' => $user->id,
            'created_at' => now(),
            'updated_at' => null,
        ]);

        $groupNames = explode(',', $request->group_name);
        $groupNames = array_map('trim', $groupNames);

        $group_details = MST_group::whereIn('group_name', $groupNames)->where('user_ID', $user->id)->where('isActive', 1)->get();

        $groupIds = $group_details->pluck('id')->toArray();

        // Fetch numbers from contact_details
        $contacts = ContactDetails::whereIn('group_ID', $groupIds)->where('user_ID', $user->id)->pluck('contact_no')->toArray();

        // Deduplicate & clean
        $numbers = array_unique(array_map('trim', $contacts));

        //  Remove duplicate mobile numbers
        $numbers = self::removeDuplicateNumbers($numbers);

        dispatch(new SendUrlTrackingKeyJob($numbers, $message, $user->id, $sender_ID));
        dispatch(new SendNumbersJob($numbers, $message, $user->id, $request->message, $content_type_ID, $DND_check_ID, $route_id, $gatewayDistribution));

        return $message;
    }

     public static function validateStrictTemplate(string $template, string $message)
    {
        if (strpos($template, '{#var#}') === false) {
            Log::debug('[TemplateValidation] SUCCESS - ALL-STATIC template (no {#var#}), skipping validation');
            return ['status' => true, 'message' => 'Template matched successfully.'];
        }
        
        Log::debug('[TemplateValidation] Starting', [
            'template' => $template,
            'message'  => $message,
        ]);

        $templateParts = [];
        $buffer = '';
        $insidePlaceholder = false;

        for($i = 0; $i < strlen($template); $i++)
        {
            $char = $template[$i];

            if($char === '{')
            {
                if($buffer !== '')
                {
                    $templateParts[] = ['type' => 'text', 'value' => $buffer];
                    $buffer = '';
                }

                $insidePlaceholder = true;
                $buffer .= $char;
            }
            elseif($char === '}')
            {
                $buffer .= $char;
                $templateParts[] = ['type' => 'placeholder', 'value' => $buffer];
                $buffer = '';
                $insidePlaceholder = false;
            }
            else
            {
                $buffer .= $char;
            }
        }

        if($buffer !== '')
        {
            $templateParts[] = ['type' => $insidePlaceholder ? 'placeholder' : 'text', 'value' => $buffer];
        }

        Log::debug('[TemplateValidation] Parsed template parts', ['parts' => $templateParts]);

        $messageCursor = 0;
        $templateIndex = 0;
        $templateCount = count($templateParts);

        while($templateIndex < $templateCount && $messageCursor < strlen($message))
        {
            $part = $templateParts[$templateIndex];

            if($part['type'] === 'text')
            {
                $expected = $part['value'];
                $expectedLength = strlen($expected);
                $actual = substr($message, $messageCursor, $expectedLength);

                if($actual === $expected)
                {
                    $messageCursor += $expectedLength;
                    $templateIndex++;
                }
                else
                {
                    $flexibleMatch = self::flexibleTextMatch($expected, $message, $messageCursor);

                    if($flexibleMatch['matched'])
                    {
                        $messageCursor = $flexibleMatch['newPosition'];
                        $templateIndex++;
                    }
                    else
                    {
                        Log::warning('[TemplateValidation] MISMATCH on text part', [
                            'part_index'  => $templateIndex,
                            'expected'    => $expected,
                            'got'         => substr($message, $messageCursor, $expectedLength),
                            'msg_cursor'  => $messageCursor,
                            'msg_context' => substr($message, $messageCursor, 80),
                        ]);
                        return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
                    }
                }
            }
            else
            {
                $nextStaticText = '';
                $nextPartIndex = $templateIndex + 1;

                while($nextPartIndex < $templateCount)
                {
                    if($templateParts[$nextPartIndex]['type'] === 'text')
                    {
                        $nextStaticText = $templateParts[$nextPartIndex]['value'];
                        break;
                    }

                    $nextPartIndex++;
                }

                if($nextStaticText !== '')
                {
                    $pos = strpos($message, $nextStaticText, $messageCursor);

                    if($pos === false)
                    {
                        $flexibleMatch = self::flexibleTextSearch($nextStaticText, $message, $messageCursor);

                        if($flexibleMatch['found'])
                        {
                            $pos = $flexibleMatch['position'];
                        }
                        else
                        {
                            Log::warning('[TemplateValidation] MISMATCH - next static text not found after placeholder', [
                                'part_index'       => $templateIndex,
                                'placeholder'      => $part['value'],
                                'next_static_text' => $nextStaticText,
                                'msg_cursor'       => $messageCursor,
                                'msg_context'      => substr($message, $messageCursor, 100),
                            ]);
                            return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
                        }
                    }

                    $placeholderValue = substr($message, $messageCursor, $pos - $messageCursor);

                    if($placeholderValue === '')
                    {
                        Log::warning('[TemplateValidation] MISMATCH - empty placeholder value', [
                            'part_index'       => $templateIndex,
                            'placeholder'      => $part['value'],
                            'next_static_text' => $nextStaticText,
                            'msg_cursor'       => $messageCursor,
                        ]);
                        return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
                    }

                    Log::debug('[TemplateValidation] Placeholder matched', [
                        'part_index'        => $templateIndex,
                        'placeholder'       => $part['value'],
                        'placeholder_value' => $placeholderValue,
                    ]);

                    $messageCursor = $pos;
                    $templateIndex++;
                }
                else
                {
                    $placeholderValue = substr($message, $messageCursor);

                    if($placeholderValue === '')
                    {
                        Log::warning('[TemplateValidation] MISMATCH - trailing placeholder has no value', [
                            'part_index'  => $templateIndex,
                            'placeholder' => $part['value'],
                            'msg_cursor'  => $messageCursor,
                        ]);
                        return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
                    }

                    Log::debug('[TemplateValidation] Trailing placeholder matched', [
                        'part_index'        => $templateIndex,
                        'placeholder'       => $part['value'],
                        'placeholder_value' => $placeholderValue,
                    ]);

                    $messageCursor = strlen($message);
                    $templateIndex++;
                }
            }
        }

        if($templateIndex !== $templateCount || $messageCursor !== strlen($message))
        {
            Log::warning('[TemplateValidation] MISMATCH - incomplete match at end', [
                'template_index'  => $templateIndex,
                'template_count'  => $templateCount,
                'msg_cursor'      => $messageCursor,
                'msg_length'      => strlen($message),
                'remaining_msg'   => substr($message, $messageCursor, 100),
                'remaining_parts' => array_slice($templateParts, $templateIndex),
            ]);
            return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
        }

        Log::debug('[TemplateValidation] SUCCESS - template matched');
        return ['status' => true, 'message' => 'Template matched successfully.'];
    }

    private static function flexibleTextMatch(string $expected, string $message, int $startPosition): array
    {
        $expectedLength = strlen($expected);
        $messageLength = strlen($message);
        $expectedIndex = 0;
        $messageIndex = $startPosition;

        // Skip leading whitespace in message
        while($messageIndex < $messageLength && ctype_space($message[$messageIndex]))
        {
            $messageIndex++;
        }

        while($expectedIndex < $expectedLength && $messageIndex < $messageLength)
        {
            $expectedChar = $expected[$expectedIndex];
            $messageChar = $message[$messageIndex];

            // FIX 1: Both sides are at a URL — skip both URLs regardless of content.
            // Template may have a registered static URL (e.g. https://bit.ly/MOBSTE)
            // while the message has the tracking placeholder URL.
            // SendNumbersJob::replaceUrlParameters() swaps it per-number, so both are valid.
            if ($expectedChar === 'h' &&
                substr($expected, $expectedIndex, 4) === 'http' &&
                $messageChar === 'h' &&
                substr($message, $messageIndex, 4) === 'http')
            {
                while($expectedIndex < $expectedLength && !ctype_space($expected[$expectedIndex]))
                {
                    $expectedIndex++;
                }
                while($messageIndex < $messageLength && !ctype_space($message[$messageIndex]))
                {
                    $messageIndex++;
                }
                continue;
            }

            if($expectedChar === $messageChar)
            {
                $expectedIndex++;
                $messageIndex++;
            }
            // FIX 2: Both sides are whitespace but different types (\n vs space, \r\n vs space, etc.)
            // Skip all consecutive whitespace on both sides so line-break differences don't fail.
            elseif(ctype_space($expectedChar) && ctype_space($messageChar))
            {
                while($expectedIndex < $expectedLength && ctype_space($expected[$expectedIndex]))
                {
                    $expectedIndex++;
                }
                while($messageIndex < $messageLength && ctype_space($message[$messageIndex]))
                {
                    $messageIndex++;
                }
            }
            elseif(ctype_space($expectedChar) && !ctype_space($messageChar))
            {
                $expectedIndex++;
            }
            elseif(!ctype_space($expectedChar) && ctype_space($messageChar))
            {
                $messageIndex++;
            }
            else
            {
                return ['matched' => false, 'newPosition' => $messageIndex];
            }
        }

        // Skip trailing whitespace in message
        while($messageIndex < $messageLength && ctype_space($message[$messageIndex]))
        {
            $messageIndex++;
        }

        if($expectedIndex === $expectedLength)
        {
            return ['matched' => true, 'newPosition' => $messageIndex];
        }

        return ['matched' => false, 'newPosition' => $messageIndex];
    }

    private static function flexibleTextSearch(string $searchText, string $message, int $startPosition): array
    {
        $messageLength = strlen($message);

        for($i = $startPosition; $i < $messageLength; $i++)
        {
            $match = self::flexibleTextMatch($searchText, $message, $i);
            
            if($match['matched'])
            {
                return ['found' => true, 'position' => $i];
            }
        }

        return ['found' => false, 'position' => -1];
    }

    /* public static function validateStrictTemplate(string $template, string $message)
    {
        $templateParts = [];
        $buffer = '';
        $insidePlaceholder = false;

        for($i = 0; $i < strlen($template); $i++) 
        {
            $char = $template[$i];

            if($char === '{') 
            {
                if($buffer !== '') 
                {
                    $templateParts[] = ['type' => 'text', 'value' => $buffer];
                    $buffer = '';
                }

                $insidePlaceholder = true;
                $buffer .= $char;
            } 
            elseif($char === '}') 
            {
                $buffer .= $char;
                $templateParts[] = ['type' => 'placeholder', 'value' => $buffer];
                $buffer = '';
                $insidePlaceholder = false;
            } 
            else 
            {
                $buffer .= $char;
            }
        }

        if($buffer !== '') 
        {
            $templateParts[] = ['type' => $insidePlaceholder ? 'placeholder' : 'text', 'value' => $buffer];
        }

        $expectedSegments = [];
        
        foreach($templateParts as $part)
        {
            
            if($part['type'] === 'text') 
            {
                $expectedSegments[] = $part['value'];
            } 
            else 
            {
                $expectedSegments[] = null;
            }
        }

        $messageCursor = 0;

        for($i = 0; $i < count($expectedSegments); $i++) 
        {
            $expected = $expectedSegments[$i];

            if($expected !== null) 
            {
                $len = strlen($expected);
                $actual = substr($message, $messageCursor, $len);

                if($actual !== $expected) 
                {
                    return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
                }

                $messageCursor += $len;
            } 
            else 
            {
                $nextStaticText = '';

                for($j = $i + 1; $j < count($expectedSegments); $j++) 
                {
                    if($expectedSegments[$j] !== null) 
                    {
                        $nextStaticText = $expectedSegments[$j];
                        break;
                    }
                }

                if($nextStaticText !== '') 
                {
                    $pos = strpos($message, $nextStaticText, $messageCursor);

                    if($pos === false) 
                    {
                        return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
                    }

                    $placeholderValue = substr($message, $messageCursor, $pos - $messageCursor);

                    if($placeholderValue === '' || preg_match('/\s/', $placeholderValue)) 
                    {
                        return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
                    }

                    $messageCursor = $pos;
                } 
                else 
                {
                    $placeholderValue = substr($message, $messageCursor);
                    
                    if($placeholderValue === '' || preg_match('/\s/', $placeholderValue)) 
                    {
                        return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
                    }

                    $messageCursor = strlen($message);
                }
            }
        }

        if($messageCursor !== strlen($message)) 
        {
            return ['status' => false, 'message' => 'Template Mismatched! Your message does not match the selected template format.'];
        }

        return ['status' => true, 'message' => 'Template matched successfully.'];
    } */

    public static function processDynamicFile($file)
    {
        try 
        {
            $spreadsheet = IOFactory::load($file->getPathname());
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray();

            if(count($rows) < 2) 
            {
                return [
                    'status' => false,
                    'message' => 'No data found',
                    'code' => 400
                ];
            }

            $header = $rows[0];
            $dataRows = array_slice($rows, 1);
            $preview = array_merge([ $header ], array_slice($dataRows, 0, 3 ));

            //  To
            $numericColumnIndex = null;
            
            foreach($header as $index => $columnName) 
            {
                $isNumeric = true;
                
                foreach($dataRows as $row) 
                {
                    if(!isset($row[$index]) || !is_numeric($row[$index])) 
                    {
                        $isNumeric = false;
                        break;
                    }
                }

                if($isNumeric) 
                {
                    $numericColumnIndex = $index;
                    break;
                }
            }

            $to = $numericColumnIndex !== null ? $header[$numericColumnIndex] : null;

            //  Pleaceholders
            $placeholders = [];
            
            foreach($header as $index => $columnName) 
            {
                /* if($index !== $numericColumnIndex) 
                {
                    $placeholders[] = $columnName;
                } */

                $placeholders[] = $columnName;
            }

            //  All Data
            $allData = [];
            foreach($dataRows as $row) 
            {
                $item = [];

                foreach($header as $i => $col) 
                {
                    $item[$col] = $row[$i] ?? null;
                }

                $allData[] = $item;
            }

            return [
                'status' => true,
                'code' => 200,
                'preview' => $preview,
                'to' => $to,
                'placeholder' => $placeholders,
                'all_data' => $allData
            ];
        } 
        catch(\Exception $e) 
        {
            return [
                'status' => false,
                'message' => 'Error processing file: ' . $e->getMessage(),
                'code' => 500
            ];
        }
    }

    // public static function generatePreviewMessages(Request $request): array
    // {
    //     $template = $request->input('template');
    //     $rows = $request->input('rows');

    //     $messages = [];

    //     foreach($rows as $row) 
    //     {
    //         $message = $template;

    //         foreach($row as $key => $value) 
    //         {
    //             $message = str_replace('{' . $key . '}', $value ?? '', $message);
    //         }

    //         $charCount = mb_strlen($message);
    //         $isUnicode = preg_match('/[^\x00-\x7F]/', $message);
    //         $limit = $isUnicode ? 70 : 160;
    //         $bill = ceil($charCount / $limit);

    //         $messages[] = [
    //             'number' => $row['number'] ?? '',
    //             'message' => $message,
    //             'char_count' => $charCount,
    //             'bill' => $bill,
    //         ];
    //     }

    //     return $messages;
    // }

    // public static function generatePreviewMessages(Request $request): array
    // {
    //     // Increase memory and time limits for large datasets
    //     ini_set('memory_limit', '-1');
    //     ini_set('max_execution_time', 0);
        
    //     $template = $request->input('template');
    //     $rows = $request->input('rows');
        
    //     $messages = [];
    //     $totalRows = count($rows);
        
    //     // Process in chunks for memory efficiency
    //     $chunkSize = 1000;
        
    //     for ($i = 0; $i < $totalRows; $i += $chunkSize) {
    //         $chunk = array_slice($rows, $i, $chunkSize);
            
    //         foreach($chunk as $row) 
    //         {
    //             $message = $template;

    //             foreach($row as $key => $value) 
    //             {
    //                 $message = str_replace('{' . $key . '}', $value ?? '', $message);
    //             }

    //             $charCount = mb_strlen($message);
    //             $isUnicode = preg_match('/[^\x00-\x7F]/', $message);
    //             $limit = $isUnicode ? 70 : 160;
    //             $bill = ceil($charCount / $limit);

    //             $messages[] = [
    //                 'number' => $row['number'] ?? '',
    //                 'message' => $message,
    //                 'char_count' => $charCount,
    //                 'bill' => $bill,
    //             ];
    //         }
            
    //         // Clear memory periodically
    //         if ($i % 5000 === 0) {
    //             gc_collect_cycles();
    //         }
    //     }

    //     return $messages;
    // }
    public static function generatePreviewMessages(Request $request): array
    {

        try {
            // Increase memory and time limits
            ini_set('memory_limit', '-1');
            set_time_limit(0);

            $template = $request->input('template');
            $rows = $request->input('rows');

            // Validate inputs
            if (empty($template)) {
                throw new \Exception('Template is required');
            }

            if (!is_array($rows) || empty($rows)) {
                throw new \Exception('Rows must be a non-empty array');
            }

            $messages = [];
            $totalRows = count($rows);

            // Auto-detect the phone number column from the first row
            $numberField = 'number'; // default
            if (!empty($rows[0]) && is_array($rows[0])) {
                $firstRowKeys = array_keys($rows[0]);
                $lowerKeys = array_map(function($k) { return strtolower(trim($k)); }, $firstRowKeys);

                $possiblePhoneColumns = ['number', 'phone', 'mobile', 'contact_no', 'contactno', 'contact no', 'phone_no', 'phonenumber', 'mobile_number', 'mobilenumber'];

                foreach ($possiblePhoneColumns as $possible) {
                    $foundIndex = array_search($possible, $lowerKeys, true);
                    if ($foundIndex !== false) {
                        $numberField = $firstRowKeys[$foundIndex]; // Use original case key
                        break;
                    }
                }

            }

            // Process all rows
            $processedCount = 0;
            $failedCount = 0;

            foreach($rows as $index => $row)
            {
                if (!is_array($row)) {
                    $failedCount++;
                    continue;
                }

                if (!isset($row[$numberField])) {
                    if ($index < 3) { 
                    }
                    $failedCount++;
                    continue;
                }

                $message = $template;

                // Replace placeholders
                foreach($row as $key => $value)
                {
                    if (!is_string($key)) {
                        continue;
                    }

                    // $placeholder = '{' . $key . '}';
                    // $message = str_replace($placeholder, $value ?? '', $message);

                    $message = str_replace('{' . $key . '}', $value ?? '', $message);
                    $message = str_replace('[' . $key . ']', $value ?? '', $message);

                    // DLT template variable syntax: {#var#} is a fixed single-slot
                    // placeholder, not keyed by column name — sourced from the row's
                    // "var" column.
                    if (strtolower(trim($key)) === 'var') {
                        $message = str_replace('{#var#}', $value ?? '', $message);
                    }
                }

                $charCount = mb_strlen($message);
                $isUnicode = preg_match('/[^\x00-\x7F]/', $message);
                $limit = $isUnicode ? 70 : 160;
                $bill = ceil($charCount / $limit);

                $messages[] = [
                    'number' => $row[$numberField] ?? '',
                    'message' => $message,
                    'char_count' => $charCount,
                    'bill' => $bill,
                ];

                $processedCount++;
            }

            return $messages;
            
        } catch (\Exception $e) {
            
            throw $e;
        }
    }

    private static function removeDuplicateNumbers($numbers)
    {
        $normalizedNumbers = [];
        $uniqueNumbers = [];
        $seen = [];

        foreach($numbers as $number)
        {
            $cleanNumber = preg_replace('/[^0-9]/', '', $number);

            $normalizedNumber = self::normalizePhoneNumber($cleanNumber);

            $key = 'n' . $normalizedNumber;
            if(!isset($seen[$key]))
            {
                $seen[$key] = true;
                $normalizedNumbers[] = $normalizedNumber;
                $uniqueNumbers[] = $number;
            }
        }

        return $uniqueNumbers;
    }

    private static function normalizePhoneNumber($number)
    {
        $number = ltrim($number, '0');
        
        if(strpos($number, '91') === 0) 
        {
            $withoutCode = substr($number, 2);
            
            if(strlen($withoutCode) === 10) 
            {
                $number = $withoutCode;
            }
        }
        
        if(strlen($number) === 10 && preg_match('/^[6-9]\d{9}$/', $number)) 
        {
            return $number;
        }
        
        return $number;
    }

    private static function removeDynamicDuplicateNumbers($numbers)
    {
        $normalizedNumbers = [];
        $uniqueMessages = [];

        foreach($numbers as $number) 
        {
            $cleanNumber = preg_replace('/[^0-9]/', '', $number);
            $normalizedNumber = self::normalizeDynamicPhoneNumber($cleanNumber);

            if(!in_array($normalizedNumber, $normalizedNumbers)) 
            {
                $normalizedNumbers[] = $normalizedNumber;
                $uniqueMessages[] = $number;
            }
        }

        return $uniqueMessages;
    }

    private static function normalizeDynamicPhoneNumber($number)
    {
        $number = ltrim($number, '0');

        if(strpos($number, '91') === 0) 
        {
            $withoutCode = substr($number, 2);
            
            if(strlen($withoutCode) === 10) 
            {
                $number = $withoutCode;
            }
        }

        if(strlen($number) === 10 && preg_match('/^[6-9]\d{9}$/', $number)) 
        {
            return $number;
        }

        return $number;
    }

    // If sender_details has a direct user_id column
    public static function getAssignedSenderIds($user_id)
    {
        return DB::table('sender_details')
            ->where('user_id', $user_id)
            ->where('isActive', 1)
            ->whereNull('deleted_at')
            ->select(
                'id',
                'sender_ID'
            )
            ->orderBy('sender_details.sender_ID', 'asc')
            ->get();
    }

    public static function getTodaySmsReportSummary($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit      = $params['limit'];
        $offset     = $params['offset'];
        $searchText = $params['searchText'];
        $sorting    = $params['sorting'];
        $orderBy    = $params['orderBy'];

        $user_ID = $filters['user_ID'] ?? null;
        $role_ID = $filters['role_ID'] ?? null;
        $sender_ID = $filters['sender_ID'] ?? null;

        // Sub-query 1: regular send_numbers for today
        $sendQuery = DB::table('send_numbers')
            ->join('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1)
            ->whereDate('send_numbers.created_at', today())
            ->when($user_ID && $user_ID !== 'All', fn($q) => $q->where('send_numbers.userids', $user_ID))
            ->when($role_ID && $role_ID !== 'All', fn($q) => $q->where('users.role_id', $role_ID))
            ->when($sender_ID && $sender_ID !== 'All', fn($q) => $q->where('sender_details.sender_ID', $sender_ID))
            ->when(empty($user_ID) && empty($role_ID), fn($q) => $q->where('send_numbers.userids', $user->id))
            ->select(
                'users.id as user_ID',
                'users.username',
                'send_numbers.status',
                'send_numbers.err_code'
            );

        // Sub-query 2: schedule_send_numbers for today
        $scheduleQuery = DB::table('schedule_send_numbers')
            ->join('users', 'users.id', '=', 'schedule_send_numbers.userids')
            ->whereNull('schedule_send_numbers.deleted_at')
            ->whereDate('schedule_send_numbers.created_at', today())
            ->when($user_ID && $user_ID !== 'All', fn($q) => $q->where('schedule_send_numbers.userids', $user_ID))
            ->when($role_ID && $role_ID !== 'All', fn($q) => $q->where('users.role_id', $role_ID))
            ->when($sender_ID && $sender_ID !== 'All', fn($q) => $q->where('schedule_send_numbers.senderid', $sender_ID))
            ->when(empty($user_ID) && empty($role_ID), fn($q) => $q->where('schedule_send_numbers.userids', $user->id))
            ->select(
                'users.id as user_ID',
                'users.username',
                'schedule_send_numbers.status',
                'schedule_send_numbers.err_code'
            );

        // UNION ALL both sources
        $union = $sendQuery->unionAll($scheduleQuery);

        // Outer query: group by user and compute counts
        $query = DB::table(DB::raw("({$union->toSql()}) as combined"))
            ->mergeBindings($union)
            ->when($searchText, function ($q) use ($searchText) {
                $lowerSearch = strtolower($searchText);
                $q->where(function ($q2) use ($lowerSearch) {
                    $q2->whereRaw('LOWER(username) LIKE ?', ['%' . $lowerSearch . '%']);
                });
            })
            ->select(
                'user_ID',
                'username',
                DB::raw('COUNT(*) as total'),
                DB::raw("COUNT(CASE WHEN status = 'delivered' OR err_code = '000' THEN 1 ELSE NULL END) as delivered"),
                DB::raw("COUNT(CASE WHEN status = 'failed' OR (err_code IS NOT NULL AND err_code != '000') THEN 1 ELSE NULL END) as failed"),
                DB::raw("COUNT(CASE WHEN LOWER(status) IN ('pending', 'submitted', 'scheduled') THEN 1 ELSE NULL END) as pending"),
                DB::raw('COUNT(*) as submitted')
            )
            ->groupBy('user_ID', 'username');

        // Get total count
        $totalCount = (clone $query)->get()->count();

        // Apply sorting and pagination
        if ($sorting === 'isActive') {
            $orderBy = strtolower($orderBy) === 'asc' ? 'desc' : 'asc';
        }

        $allowedSortColumns = ['user_ID', 'username', 'total', 'delivered', 'failed', 'pending', 'submitted'];

        if (in_array($sorting, $allowedSortColumns)) {
            $query->orderBy($sorting, $orderBy);
        } else {
            $query->orderBy('total', 'desc');
        }

        $records = $query
            ->skip($offset)
            ->take($limit)
            ->get();

        $formattedRecords = $records->map(function ($record) {
            return [
                'user_ID' => $record->user_ID,
                'username' => $record->username,
                'total' => $record->total,
                'submitted' => $record->submitted,
                'delivered' => $record->delivered,
                'failed' => $record->failed,
                'pending' => $record->pending,
            ];
        });

        return [['records' => $formattedRecords->toArray()], $totalCount];
    }

    public static function getTodaySmsReport($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit      = $params['limit'];
        $offset     = $params['offset'];
        $searchText = $params['searchText'];
        $sorting    = $params['sorting'];
        $orderBy    = $params['orderBy'];

        $user_ID   = $filters['user_ID'] ?? null;
        $role_ID   = $filters['role_ID'] ?? null;
        $sender_ID = $filters['sender_ID'] ?? null;
        $status    = isset($filters['status']) ? strtolower($filters['status']) : null;

        $errorCodeLookup = DB::table('error_code')
            ->select('error_code', DB::raw('MAX(error_status) as error_status'))
            ->whereNull('deleted_at')
            ->where('isDeleted', 0)
            ->groupBy('error_code');

        // Sub-query 1: regular send_numbers for today
        $sendQuery = DB::table('send_numbers')
            ->join('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoinSub($errorCodeLookup, 'error_code', function ($join) {
                $join->on('error_code.error_code', '=', 'send_numbers.err_code');
            })
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1)
            ->whereDate('send_numbers.created_at', today())
            ->when($user_ID && $user_ID !== 'All', fn($q) => $q->where('send_numbers.userids', $user_ID))
            ->when($role_ID && $role_ID !== 'All', fn($q) => $q->where('users.role_id', $role_ID))
            ->when($sender_ID && $sender_ID !== 'All', fn($q) => $q->where('sender_details.sender_ID', $sender_ID))
            ->when(empty($user_ID) && empty($role_ID), fn($q) => $q->where('send_numbers.userids', $user->id))
            ->select(
                'send_numbers.msg_job_id as job_id',
                'users.username',
                'sender_details.sender_ID',
                'send_numbers.send_msg as message',
                'send_messages.template_id',
                'send_numbers.mobile_number',
                'send_numbers.created_at as created_date',
                'send_numbers.sent_at as sent_date',
                'send_numbers.status',
                'send_numbers.err_code as error_code',
                'error_code.error_status as error_description',
                'send_numbers.route',
                'send_numbers.sent_at as sent_at',
                'send_numbers.delivered_date as DLR_Time',
                'send_numbers.char_count as chars',
                'send_numbers.msgcredit as bill'
            );

        // Sub-query 2: schedule_send_numbers for today
        $scheduleQuery = DB::table('schedule_send_numbers')
            ->join('users', 'users.id', '=', 'schedule_send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'schedule_send_numbers.senderid')
            ->leftJoin('schedule_send_messages', 'schedule_send_messages.id', '=', 'schedule_send_numbers.message_id')
            ->leftJoinSub($errorCodeLookup, 'error_code', function ($join) {
                $join->on('error_code.error_code', '=', 'schedule_send_numbers.err_code');
            })
            ->whereNull('schedule_send_numbers.deleted_at')
            ->whereDate('schedule_send_numbers.created_at', today())
            ->when($user_ID && $user_ID !== 'All', fn($q) => $q->where('schedule_send_numbers.userids', $user_ID))
            ->when($role_ID && $role_ID !== 'All', fn($q) => $q->where('users.role_id', $role_ID))
            ->when($sender_ID && $sender_ID !== 'All', fn($q) => $q->where('sender_details.sender_ID', $sender_ID))
            ->when(empty($user_ID) && empty($role_ID), fn($q) => $q->where('schedule_send_numbers.userids', $user->id))
            ->select(
                'schedule_send_numbers.msg_job_id as job_id',
                'users.username',
                'sender_details.sender_ID',
                'schedule_send_numbers.send_msg as message',
                'schedule_send_messages.template_id',
                'schedule_send_numbers.mobile_number',
                'schedule_send_numbers.created_at as created_date',
                'schedule_send_numbers.sent_at as sent_date',
                'schedule_send_numbers.status',
                'schedule_send_numbers.err_code',
                'error_code.error_status as error_description',
                'schedule_send_numbers.route',
                'schedule_send_numbers.sent_at as sent_at',
                DB::raw('NULL as DLR_Time'),
                'schedule_send_numbers.char_count as chars',
                'schedule_send_numbers.msgcredit as bill'
            );

        // UNION ALL both sources
        $union = $sendQuery->unionAll($scheduleQuery);

        // Wrap in outer query for status filter, search, sort, paginate
        $query = DB::table(DB::raw("({$union->toSql()}) as combined"))
            ->mergeBindings($union)
            ->when($status && $status !== 'all', function ($q) use ($status) {
                if ($status === 'delivered') {
                    $q->where(function ($q2) {
                        $q2->whereRaw("LOWER(status) = 'delivered'")->orWhere('error_code', '000');
                    });
                } elseif ($status === 'failed') {
                    $q->where(function ($q2) {
                        $q2->whereRaw("LOWER(status) = 'failed'")
                           ->orWhere(function ($q3) {
                               $q3->whereNotNull('error_code')->whereNotIn('error_code', ['000', '0']);
                           });
                    });
                } elseif ($status === 'pending') {
                    $q->whereRaw("LOWER(status) IN ('pending', 'submitted', 'scheduled')");
                } else {
                    $q->whereRaw('LOWER(status) = ?', [$status]);
                }
            })
            ->when($searchText, function ($q) use ($searchText) {
                $lowerSearch = strtolower($searchText);
                $q->where(function ($q2) use ($lowerSearch, $searchText) {
                    $q2->whereRaw('LOWER(username) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(sender_ID) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(status) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(error_code) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(error_description) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('mobile_number LIKE ?', ['%' . $searchText . '%'])
                    ->orWhereRaw('job_id LIKE ?', ['%' . $searchText . '%'])
                    ->orWhereRaw('created_date LIKE ?', ['%' . $searchText . '%']);
                });
            });

        // Total count
        $totalCount = (clone $query)->count();

        // Sorting
        $allowedSort = ['job_id', 'username', 'sender_ID', 'mobile_number', 'status', 'created_date', 'sent_date', 'bill', 'chars'];
        $sortColumn  = in_array($sorting, $allowedSort) ? $sorting : 'created_date';

        $today_sms_report = $query
            ->orderBy($sortColumn, $orderBy)
            ->skip($offset)
            ->take($limit)
            ->get();

        return [$today_sms_report, $totalCount];
    }
    
    public static function generateSmsDownload($user_id, $status = null, $startDate = null, $endDate = null)
    {
        $query = DB::table('send_numbers')
            ->join('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1)
            ->where('send_numbers.userids', $user_id);

        // Apply date range
        if (!empty($fromDate) && !empty($toDate)) {
            $query->whereDate('send_numbers.created_at', '>=', $startDate)
                ->whereDate('send_numbers.created_at', '<=', $endDate);
        } else {
            // Default to today if no date range provided
            $query->whereDate('send_numbers.created_at', today());
        }

       // Filter by status
        if ($status && $status !== 'All') {
            if ($status === 'delivered') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'delivered')
                    ->orWhere('send_numbers.err_code', '000');
                });
            } elseif ($status === 'failed') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'failed')
                    ->orWhere(function ($q2) {
                        $q2->whereNotNull('send_numbers.err_code')
                            ->where('send_numbers.err_code', '!=', '000');
                    });
                });
            } elseif ($status === 'pending') {
                $query->where(function ($q) {
                    $q->whereNotIn('send_numbers.status', ['delivered', 'failed'])
                    ->where(function ($q2) {
                        $q2->whereNull('send_numbers.err_code')
                            ->orWhere('send_numbers.err_code', '');
                    });
                });
            } else {
                $query->where('send_numbers.status', $status);
            }
        }

        $records = $query->select(
                'send_numbers.msg_job_id as job_id',
                'users.username',
                'sender_details.sender_ID',
                'send_numbers.send_msg as message',
                'send_messages.template_id',
                'send_numbers.mobile_number',
                'send_numbers.created_at',
                'send_numbers.sent_at',
                'send_numbers.status',
                'send_numbers.err_code',
                'error_code.error_status as error_description'
            )
            ->orderBy('send_numbers.created_at', 'asc')
            ->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000; // 4 lakh rows per file
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "SMS_Report_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            Excel::store(new SmsReportExport($chunk), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        // If only one file → return directly
        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        // If multiple files → create ZIP
        $zipFileName = 'SMS_Reports_' . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        // Clean up individual files
        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }

    public static function getTodaySmsGatewayReportSummary($user, array $filters)
    {
        $params = getListingParams($filters);
        
        $limit = $params['limit'];
        $offset = $params['offset'];
        $searchText = $params['searchText'];
        $sorting = $params['sorting'];
        $orderBy = $params['orderBy'];

        $startDate = $filters['from_date'] ?? null;
        $endDate = $filters['to_date'] ?? null;
        $gatewayName = $filters['gateway_name'] ?? null;

        $gateways = DB::table('sms_gateway')
            ->whereNull('deleted_at')
            ->get(['id', 'gateway_name']);

        $gatewayMap = [];
        foreach ($gateways as $gateway) {
            $gatewayMap[$gateway->gateway_name] = $gateway->id;
        }

        $autoSwitchGatewayIDs = DB::table('auto_switch_gateway')
            ->whereNull('deleted_at')
            ->pluck('primary_gateway_ID')
            ->toArray();

        $sendNumbersQuery = DB::table('send_numbers')
            ->whereNull('deleted_at')
            ->where('isActive', 1);

        if ($startDate) {
            $sendNumbersQuery->whereDate('created_at', '>=', $startDate);
        }
        if ($endDate) {
            $sendNumbersQuery->whereDate('created_at', '<=', $endDate);
        }

        $sendNumbers = $sendNumbersQuery->get();

        $gatewayStats = [];

        foreach ($sendNumbers as $record) {
            $serviceNames = explode(',', $record->service_id);
            
            foreach ($serviceNames as $serviceName) {
                $serviceName = trim($serviceName);
                if (empty($serviceName)) continue;

                $gatewayId = $gatewayMap[$serviceName] ?? null;

                $gatewayNameValue = $serviceName;
                $gatewayIdValue = $gatewayId ?: $serviceName;

                if (!isset($gatewayStats[$gatewayNameValue])) {
                    $gatewayStats[$gatewayNameValue] = [
                        'gateway_name' => $gatewayNameValue,
                        'gateway_ID' => $gatewayIdValue,
                        'total' => 0,
                        'submitted' => 0,
                        'delivered' => 0,
                        'failed' => 0,
                        'pending' => 0,
                        'isAutoswitch' => ($gatewayId && in_array($gatewayId, $autoSwitchGatewayIDs)) ? 1 : 0,
                    ];
                }

                $gatewayStats[$gatewayNameValue]['total']++;
                $gatewayStats[$gatewayNameValue]['submitted']++;

                $status = $record->status ?? '';
                $errCode = $record->err_code ?? '';

                if ($status === 'Delivered' || $errCode === '000') {
                $gatewayStats[$gatewayNameValue]['delivered']++;
            } elseif ($status === 'Submitted') {
                $gatewayStats[$gatewayNameValue]['pending']++; // Changed this line - count 'submitted' status as pending
            } else {
                $gatewayStats[$gatewayNameValue]['failed']++;
            }
        }
    }

        $records = array_values($gatewayStats);

        if ($gatewayName && $gatewayName !== 'All') {
            $records = array_filter($records, function($record) use ($gatewayName) {
                return $record['gateway_ID'] == $gatewayName || 
                    stripos($record['gateway_name'], $gatewayName) !== false;
            });
        }

        if ($searchText) {
            $lowerSearch = strtolower($searchText);
            $records = array_filter($records, function($record) use ($lowerSearch) {
                return stripos($record['gateway_name'], $lowerSearch) !== false ||
                    stripos((string)$record['total'], $lowerSearch) !== false ||
                    stripos((string)$record['submitted'], $lowerSearch) !== false ||
                    stripos((string)$record['delivered'], $lowerSearch) !== false ||
                    stripos((string)$record['failed'], $lowerSearch) !== false ||
                    stripos((string)$record['pending'], $lowerSearch) !== false;
            });
        }

        $totalCount = count($records);

        $allowedSortColumns = ['gateway_name', 'gateway_ID', 'total', 'submitted', 'delivered', 'failed', 'pending'];
        
        if (in_array($sorting, $allowedSortColumns)) {
            usort($records, function($a, $b) use ($sorting, $orderBy) {
                if ($orderBy === 'asc') {
                    return $a[$sorting] <=> $b[$sorting];
                } else {
                    return $b[$sorting] <=> $a[$sorting];
                }
            });
        } else {
            usort($records, function($a, $b) {
                return $b['total'] <=> $a['total'];
            });
        }

        $paginatedRecords = array_slice($records, $offset, $limit);

        return [['records' => $paginatedRecords], $totalCount];
    }

    public static function getTodaySmsGatewayReport($user, array $filters)
    {
        $params = getListingParams($filters);
        
        $limit = $params['limit'];
        $offset = $params['offset'];
        $searchText = $params['searchText'];
        $sorting = $params['sorting'];
        $orderBy = $params['orderBy'];

        $startDate = $filters['from_date'] ?? null;
        $endDate = $filters['to_date'] ?? null;
        $gatewayName = $filters['gateway_name'] ?? null;
        $status = $filters['status'] ?? null;

        $query = DB::table('send_numbers')
            ->leftJoin('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoin('sms_gateway', 'sms_gateway.gateway_name', '=', 'send_numbers.service_id')
            ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1);
        
        if ($startDate) {
            $query->whereDate('send_numbers.created_at', '>=', $startDate);
        }
        if ($endDate) {
            $query->whereDate('send_numbers.created_at', '<=', $endDate);
        }

        // Filter by gateway name
        if ($gatewayName && $gatewayName !== 'All') {
            $query->where('sms_gateway.gateway_name', $gatewayName);
        }

        // Filter by status
        if ($status && $status !== 'All') {
            if ($status === 'delivered') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'delivered')
                    ->orWhere('send_numbers.err_code', '000');
                });
            } elseif ($status === 'failed') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'failed')
                    ->orWhere(function ($q2) {
                        $q2->whereNotNull('send_numbers.err_code')
                            ->where('send_numbers.err_code', '!=', '000');
                    });
                });
            } elseif ($status === 'pending') {
                $query->where(function ($q) {
                    $q->whereNotIn('send_numbers.status', ['delivered', 'failed'])
                    ->where(function ($q2) {
                        $q2->whereNull('send_numbers.err_code')
                            ->orWhere('send_numbers.err_code', '');
                    });
                });
            } else {
                $query->where('send_numbers.status', $status);
            }
        }

        // Search functionality
        if ($searchText) {
            $query->where(function ($q) use ($searchText) {
                $lowerSearch = strtolower($searchText);
                
                if (in_array($lowerSearch, ['active', 'inactive'])) {
                    $status = $lowerSearch === 'active' ? 1 : 0;
                    $q->where('send_numbers.isActive', $status);
                } else {
                    $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(sender_details.sender_ID) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(send_messages.template_id) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(send_numbers.status) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(sms_gateway.gateway_name) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(send_numbers.err_code) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(error_code.error_status) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('send_numbers.msg_job_id LIKE ?', ['%' . $searchText . '%'])
                    ->orWhereRaw('send_numbers.mobile_number LIKE ?', ['%' . $searchText . '%'])
                    ->orWhereRaw('send_numbers.send_msg LIKE ?', ['%' . $searchText . '%'])
                    ->orWhereRaw('send_numbers.created_at LIKE ?', ['%' . $searchText . '%'])
                    ->orWhereRaw('send_numbers.sent_at LIKE ?', ['%' . $searchText . '%'])
                    ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                    ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%'])
                    ->orWhereRaw("DATE_FORMAT(send_numbers.sent_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                    ->orWhereRaw("DATE_FORMAT(send_numbers.sent_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                }
            });
        }

        $query->select(
            'send_numbers.msg_job_id as job_id',
            'users.username',
            'sender_details.sender_ID',
            'send_numbers.send_msg as message',
            'send_messages.template_id',
            'send_numbers.mobile_number',
            'send_numbers.created_at as created_date',
            'send_numbers.sent_at as sent_date',
            'send_numbers.status',
            'sms_gateway.gateway_name',
            'send_numbers.err_code as error_code',
            'error_code.error_status as error_description'
        );

        // Get total count
        $totalCount = (clone $query)->count();

        // Apply sorting and pagination - Fix ambiguous column reference
        if ($sorting === 'isActive') {
            $orderBy = strtolower($orderBy) === 'asc' ? 'desc' : 'asc';
        }

        // Handle ambiguous column names in sorting
        $sortColumn = $sorting;
        if ($sorting === 'created_at') {
            $sortColumn = 'send_numbers.created_at';
        } elseif ($sorting === 'sent_at') {
            $sortColumn = 'send_numbers.sent_at';
        }

        $records = $query->orderBy($sortColumn, $orderBy)
                        ->skip($offset)
                        ->take($limit)
                        ->get();

        return [$records, $totalCount];
    }

    public static function generateSmsGatewayDownload($startDate = null, $endDate = null, $status = null, $gatewayName = null)
    {
        $query = DB::table('send_numbers')
            ->leftJoin('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoin('sms_gateway', function($join) {
                $join->on(DB::raw('FIND_IN_SET(sms_gateway.id, send_numbers.service_id)'), '>', DB::raw('0'));
            })
            ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1);

        // Apply date range
        if ($startDate) {
            $query->whereDate('send_numbers.created_at', '>=', $startDate);
        }
        if ($endDate) {
            $query->whereDate('send_numbers.created_at', '<=', $endDate);
        }

        // Filter by gateway name/ID - FIXED LOGIC
        if ($gatewayName && $gatewayName !== 'All') {
            // First, check if the gatewayName is a numeric ID
            if (is_numeric($gatewayName)) {
                // If it's a numeric ID, get the gateway name first
                $gateway = DB::table('sms_gateway')
                    ->where('id', $gatewayName)
                    ->first();
                
                if ($gateway) {
                    $query->where(function ($q) use ($gateway) {
                        $q->where('sms_gateway.id', $gateway->id)
                        ->orWhere('send_numbers.service_id', $gateway->gateway_name)
                        ->orWhere('send_numbers.service_id', 'LIKE', $gateway->gateway_name . ',%')
                        ->orWhere('send_numbers.service_id', 'LIKE', '%,' . $gateway->gateway_name)
                        ->orWhere('send_numbers.service_id', 'LIKE', '%,' . $gateway->gateway_name . ',%');
                    });
                }
            } else {
                // If it's a gateway name, use the existing logic
                $query->where(function ($q) use ($gatewayName) {
                    $q->where('sms_gateway.id', $gatewayName)
                    ->orWhere('sms_gateway.gateway_name', $gatewayName)
                    ->orWhere('send_numbers.service_id', $gatewayName)
                    ->orWhere('send_numbers.service_id', 'LIKE', $gatewayName . ',%')
                    ->orWhere('send_numbers.service_id', 'LIKE', '%,' . $gatewayName)
                    ->orWhere('send_numbers.service_id', 'LIKE', '%,' . $gatewayName . ',%');
                });
            }
        }

        // Filter by status - Updated logic to match your requirements
        if ($status && $status !== 'All') {
            if ($status === 'delivered') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'Delivered')
                    ->orWhere('send_numbers.err_code', '000');
                });
            } elseif ($status === 'failed') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'Failed')
                    ->orWhere(function ($q2) {
                        $q2->whereNotNull('send_numbers.err_code')
                            ->where('send_numbers.err_code', '!=', '000')
                            ->where('send_numbers.err_code', '!=', '');
                    });
                });
            } elseif ($status === 'pending') {
                $query->where('send_numbers.status', 'Pending');
            } elseif ($status === 'submitted') {
                $query->where('send_numbers.status', 'Submitted');
            } else {
                $query->where('send_numbers.status', $status);
            }
        }

        $records = $query->select(
            'send_numbers.msg_job_id as job_id',
            'users.username as user',
            'sender_details.sender_ID as sender_id',
            'send_numbers.send_msg as message',
            'send_messages.template_id as template_id',
            'send_numbers.mobile_number',
            'send_numbers.created_at as created_date',
            'send_numbers.sent_at as sent_date',
            'send_numbers.status',
            'sms_gateway.gateway_name',
            'sms_gateway.id as gateway_id', // Added gateway ID
            'send_numbers.err_code as error_code',
            'error_code.error_status as error_description'
        )->orderBy('send_numbers.created_at', 'asc')->get();
        

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000; // 4 lakh rows per file
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "SMS_Gateway_Report_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            Excel::store(new SmsGatewayReportExport($chunk), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        // If only one file → return directly
        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        // If multiple files → create ZIP
        $zipFileName = 'SMS_Gateway_Reports_' . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        // Clean up individual files
        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }

    public static function getActiveGateways()
    {
        return DB::table('sms_gateway')
            ->where('isActive', 1)
            ->whereNull('deleted_at')
            ->select(
                'id',
                'gateway_name'
            )
            ->orderBy('gateway_name', 'asc')
            ->get();
    }

    public static function getSendJobsSmsSummary($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit      = $params['limit'];
        $offset     = $params['offset'];
        $searchText = $params['searchText'];
        $sorting    = $params['sorting'];
        $orderBy    = $params['orderBy'];

        $user_ID   = $filters['user_ID'] ?? null;
        $role_ID   = $filters['role_ID'] ?? null;
        $startDate = $filters['from_date'] ?? null;
        $endDate   = $filters['to_date'] ?? null;

        // Sub-query 1: send_numbers
        $sendQuery = DB::table('send_numbers')
            ->join('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1)
            ->when($startDate, fn($q) => $q->whereDate('send_numbers.created_at', '>=', $startDate))
            ->when($endDate,   fn($q) => $q->whereDate('send_numbers.created_at', '<=', $endDate))
            ->when($user_ID && $user_ID !== 'All', fn($q) => $q->where('send_numbers.userids', $user_ID))
            ->when($role_ID && $role_ID !== 'All', fn($q) => $q->where('users.role_id', $role_ID))
            ->when(empty($user_ID) && empty($role_ID), fn($q) => $q->where('send_numbers.userids', $user->id))
            ->select(
                'users.username',
                'send_numbers.userids as user_ID',
                'send_numbers.msg_job_id as job_id',
                'send_numbers.created_at',
                'send_numbers.route',
                'sender_details.sender_ID as sender',
                'send_messages.campaign_name',
                'send_numbers.send_msg',
                'send_numbers.msgcredit',
                DB::raw("CASE WHEN send_numbers.status = 'delivered' OR send_numbers.err_code = '000' THEN 1 ELSE 0 END as is_delivered")
            );

        // Sub-query 2: schedule_send_numbers
        $scheduleQuery = DB::table('schedule_send_numbers')
            ->join('users', 'users.id', '=', 'schedule_send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'schedule_send_numbers.senderid')
            ->leftJoin('schedule_send_messages', 'schedule_send_messages.id', '=', 'schedule_send_numbers.message_id')
            ->whereNull('schedule_send_numbers.deleted_at')
            ->where('schedule_send_numbers.isActive', 1)
            ->when($startDate, fn($q) => $q->whereDate('schedule_send_numbers.created_at', '>=', $startDate))
            ->when($endDate,   fn($q) => $q->whereDate('schedule_send_numbers.created_at', '<=', $endDate))
            ->when($user_ID && $user_ID !== 'All', fn($q) => $q->where('schedule_send_numbers.userids', $user_ID))
            ->when($role_ID && $role_ID !== 'All', fn($q) => $q->where('users.role_id', $role_ID))
            ->when(empty($user_ID) && empty($role_ID), fn($q) => $q->where('schedule_send_numbers.userids', $user->id))
            ->select(
                'users.username',
                'schedule_send_numbers.userids as user_ID',
                'schedule_send_numbers.msg_job_id as job_id',
                'schedule_send_numbers.created_at',
                'schedule_send_numbers.route',
                'sender_details.sender_ID as sender',
                'schedule_send_messages.campaign_name',
                'schedule_send_numbers.send_msg',
                'schedule_send_numbers.msgcredit',
                DB::raw("CASE WHEN schedule_send_numbers.status = 'delivered' OR schedule_send_numbers.err_code = '000' THEN 1 ELSE 0 END as is_delivered")
            );

        // UNION ALL both sources
        $union = $sendQuery->unionAll($scheduleQuery);

        // Outer query: group by user + job_id and aggregate
        $query = DB::table(DB::raw("({$union->toSql()}) as combined"))
            ->mergeBindings($union)
            ->when($searchText, function ($q) use ($searchText) {
                $lowerSearch = strtolower($searchText);
                $q->where(function ($q2) use ($lowerSearch, $searchText) {
                    $q2->whereRaw('LOWER(username) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('LOWER(job_id) LIKE ?', ['%' . $lowerSearch . '%'])
                    ->orWhereRaw('created_at LIKE ?', ['%' . $searchText . '%'])
                    ->orWhereRaw("DATE_FORMAT(created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                    ->orWhereRaw("DATE_FORMAT(created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                });
            })
            ->select(
                'username',
                'user_ID',
                'job_id',
                DB::raw('MIN(created_at) as created_at'),
                DB::raw('MAX(route) as route'),
                DB::raw('MAX(sender) as sender'),
                DB::raw('MAX(campaign_name) as campaign_name'),
                DB::raw('MAX(send_msg) as send_msg'),
                DB::raw('MAX(msgcredit) as msgcredit'),
                DB::raw('SUM(is_delivered) as delivery_count')
            )
            ->groupBy('username', 'user_ID', 'job_id');

        // Total count
        $totalCount = DB::table(DB::raw("({$query->toSql()}) as count_wrap"))
            ->mergeBindings($query)
            ->count();

        // Sorting & paging
        $query->orderBy('created_at', 'desc');

        $records = $query->skip($offset)->take($limit)->get();

        $formattedRecords = $records->map(function ($record) {
            return [
                'jobid'          => $record->job_id,
                'username'       => $record->username,
                'userid'         => $record->user_ID,
                'created_at'     => $record->created_at,
                'route'          => $record->route,
                'sender'         => $record->sender,
                'campaign_name'  => $record->campaign_name,
                'messages'       => $record->send_msg,
                'bill'           => $record->msgcredit,
                'delivery_count' => $record->delivery_count,
            ];
        });

        return [['records' => $formattedRecords->toArray()], $totalCount];
    }

    // public static function getSendJobsSmsReport($user, array $filters)
    // {
    //     $params = getListingParams($filters);

    //     $limit = $params['limit'];
    //     $offset = $params['offset'];
    //     $searchText = $params['searchText'];
    //     $sorting = $params['sorting'];
    //     $orderBy = $params['orderBy'];

    //     $user_ID = $filters['user_ID'] ?? null;
    //     $role_ID = $filters['role_ID'] ?? null;
    //     $startDate = $filters['from_date'] ?? null;
    //     $endDate = $filters['to_date'] ?? null;
    //     $status = $filters['status'] ?? null;
    //     $job_id = $filters['job_id'] ?? null;

    //     $query = DB::table('send_numbers')
    //         ->distinct() 
    //         ->leftJoin('users', 'users.id', '=', 'send_numbers.userids')
    //         ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
    //         ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
    //         ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
    //         ->whereNull('send_numbers.deleted_at')
    //         ->where('send_numbers.isActive', 1);
        
    //     // Date range filter - only apply if dates are provided
    //     if ($startDate) {
    //         $query->whereDate('send_numbers.created_at', '>=', $startDate);
    //     }
    //     if ($endDate) {
    //         $query->whereDate('send_numbers.created_at', '<=', $endDate);
    //     }

    //     $query->when($user_ID && $user_ID !== 'All', function ($query) use ($user_ID) {
    //             $query->where('send_numbers.userids', $user_ID);
    //         })
    //         ->when($role_ID && $role_ID !== 'All', function ($query) use ($role_ID) {
    //             $query->where('users.role_id', $role_ID);
    //         })
    //         ->when($job_id, function ($query) use ($job_id) {
    //             $query->where('send_numbers.msg_job_id', $job_id);
    //         })
    //         ->when($status && $status !== 'All', function ($query) use ($status) {
    //             if ($status === 'delivered') {
    //                 $query->where(function ($q) {
    //                     $q->where('send_numbers.status', 'delivered')
    //                     ->orWhere('send_numbers.err_code', '000');
    //                 });
    //             } elseif ($status === 'failed') {
    //                 $query->where(function ($q) {
    //                     $q->where('send_numbers.status', 'failed')
    //                     ->orWhere(function ($q2) {
    //                         $q2->whereNotNull('send_numbers.err_code')
    //                             ->where('send_numbers.err_code', '!=', '000');
    //                     });
    //                 });
    //             } elseif ($status === 'pending') {
    //                 $query->where(function ($q) {
    //                     $q->whereNotIn('send_numbers.status', ['delivered', 'failed'])
    //                     ->where(function ($q2) {
    //                         $q2->whereNull('send_numbers.err_code')
    //                             ->orWhere('send_numbers.err_code', '');
    //                     });
    //                 });
    //             } else {
    //                 $query->where('send_numbers.status', $status);
    //             }
    //         })

    //         ->when($searchText, function ($query) use ($searchText) {
    //             $query->where(function ($q) use ($searchText) {
    //                 $lowerSearch = strtolower($searchText);

    //                 if (in_array($lowerSearch, ['active', 'inactive'])) {
    //                     $status = $lowerSearch === 'active' ? 1 : 0;
    //                     $q->where('send_numbers.isActive', $status);
    //                 } else {
    //                     $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
    //                     ->orWhereRaw('LOWER(sender_details.sender_ID) LIKE ?', ['%' . $lowerSearch . '%'])
    //                     ->orWhereRaw('LOWER(send_messages.template_id) LIKE ?', ['%' . $lowerSearch . '%'])
    //                     ->orWhereRaw('LOWER(send_numbers.status) LIKE ?', ['%' . $lowerSearch . '%'])
    //                     ->orWhereRaw('LOWER(send_numbers.err_code) LIKE ?', ['%' . $lowerSearch . '%'])
    //                     ->orWhereRaw('LOWER(error_code.error_status) LIKE ?', ['%' . $lowerSearch . '%'])
    //                     ->orWhereRaw('send_numbers.msg_job_id LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.master_job_id LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.mobile_number LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.send_msg LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.created_at LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.sent_at LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.route LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.char_count LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.msgcredit LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw('send_numbers.delivered_date LIKE ?', ['%' . $searchText . '%'])
    //                     ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
    //                     ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%'])
    //                     ->orWhereRaw("DATE_FORMAT(send_numbers.sent_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
    //                     ->orWhereRaw("DATE_FORMAT(send_numbers.sent_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%'])
    //                     ->orWhereRaw("DATE_FORMAT(send_numbers.delivered_date, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
    //                     ->orWhereRaw("DATE_FORMAT(send_numbers.delivered_date, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%'])
    //                      ->orWhereRaw("TIME(send_numbers.delivered_date) LIKE ?", ['%' . $searchText . '%']);
    //                 }
    //             });
    //         })
    //         ->select(
    //             'send_numbers.id',
    //             'send_numbers.msg_job_id as job_id',
    //             'send_numbers.master_job_id as message_ID',
    //             DB::raw('MAX(users.username) as username'),
    //             DB::raw('MAX(sender_details.sender_ID) as sender_ID'),
    //             DB::raw('MAX(send_numbers.send_msg) as message'),
    //             DB::raw('MAX(send_messages.template_id) as template_id'),
    //             DB::raw('MAX(send_numbers.mobile_number) as mobile_number'),
    //             DB::raw('MAX(send_numbers.created_at) as created_date'),
    //             DB::raw('MAX(send_numbers.sent_at) as sent_date'),
    //             DB::raw('MAX(send_numbers.status) as status'),
    //             DB::raw('MAX(send_numbers.err_code) as error_code'),
    //             DB::raw('MAX(error_code.error_status) as error_description'),
    //             DB::raw('MAX(send_numbers.route) as route'),
    //             DB::raw('MAX(send_numbers.sent_at) as sent_at'),
    //             DB::raw('MAX(TIME(send_numbers.delivered_date)) as DLR'),
    //             DB::raw('MAX(send_numbers.char_count) as chars'),
    //             DB::raw('MAX(send_numbers.msgcredit) as bill')
    //         )
    //         ->groupBy('send_numbers.id', 'send_numbers.msg_job_id', 'send_numbers.master_job_id');

    //     // Get total count
    //     $totalCount = (clone $query)->count();

    //     // Apply sorting and pagination
    //     if ($sorting === 'isActive') {
    //         $orderBy = strtolower($orderBy) === 'asc' ? 'desc' : 'asc';
    //     }

    //     // Handle ambiguous column names in sorting
    //     $sortColumn = $sorting;
    //     if ($sorting === 'created_at') {
    //         $sortColumn = 'send_numbers.created_at';
    //     } elseif ($sorting === 'sent_at') {
    //         $sortColumn = 'send_numbers.sent_at';
    //     } elseif ($sorting === 'delivered_date' || $sorting === 'DLR') {
    //         $sortColumn = 'send_numbers.delivered_date';
    //     }

    //     $records = $query->orderBy($sortColumn, $orderBy)
    //                     ->skip($offset)
    //                     ->take($limit)
    //                     ->get();

    //     return [$records, $totalCount];
    // }

    public static function getSendJobsSmsReport($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit = $params['limit'];
        $offset = $params['offset'];
        $searchText = $params['searchText'];
        $sorting = $params['sorting'];
        $orderBy = $params['orderBy'];

        $user_ID = $filters['user_ID'] ?? null;
        $role_ID = $filters['role_ID'] ?? null;
        $startDate = $filters['from_date'] ?? null;
        $endDate = $filters['to_date'] ?? null;
        $status = $filters['status'] ?? null;
        $job_id = $filters['job_id'] ?? null;

        $baseQuery = DB::table('send_numbers')
            ->select('send_numbers.id')
            ->leftJoin('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1);

        if ($startDate) {
            $baseQuery->whereDate('send_numbers.created_at', '>=', $startDate);
        }
        if ($endDate) {
            $baseQuery->whereDate('send_numbers.created_at', '<=', $endDate);
        }

        if ($user_ID && $user_ID !== 'All') {
            $baseQuery->where('send_numbers.userids', $user_ID);
        }
        if ($role_ID && $role_ID !== 'All') {
            $baseQuery->where('users.role_id', $role_ID);
        }

        if ($job_id) {
            $baseQuery->where('send_numbers.msg_job_id', $job_id);
        }

        // Apply status filter
        if ($status && $status !== 'All') {
            if ($status === 'delivered') {
                $baseQuery->where(function ($q) {
                    $q->where('send_numbers.status', 'delivered')
                    ->orWhere('send_numbers.err_code', '000');
                });
            } elseif ($status === 'failed') {
                $baseQuery->where(function ($q) {
                    $q->where('send_numbers.status', 'failed')
                    ->orWhere(function ($q2) {
                        $q2->whereNotNull('send_numbers.err_code')
                            ->where('send_numbers.err_code', '!=', '000');
                    });
                });
            } elseif ($status === 'pending') {
                $baseQuery->where(function ($q) {
                    $q->whereNotIn('send_numbers.status', ['delivered', 'failed'])
                    ->where(function ($q2) {
                        $q2->whereNull('send_numbers.err_code')
                            ->orWhere('send_numbers.err_code', '');
                    });
                });
            } else {
                $baseQuery->where('send_numbers.status', $status);
            }
        }

        if ($searchText) {
            $baseQuery->where(function ($q) use ($searchText) {
                $lowerSearch = strtolower($searchText);

                if (in_array($lowerSearch, ['active', 'inactive'])) {
                    $status = $lowerSearch === 'active' ? 1 : 0;
                    $q->where('send_numbers.isActive', $status);
                } else {
                    $q->where(function ($subQuery) use ($searchText, $lowerSearch) {
                        $subQuery->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                            ->orWhereRaw('LOWER(sender_details.sender_ID) LIKE ?', ['%' . $lowerSearch . '%'])
                            ->orWhereRaw('LOWER(send_messages.template_id) LIKE ?', ['%' . $lowerSearch . '%'])
                            ->orWhereRaw('LOWER(send_numbers.status) LIKE ?', ['%' . $lowerSearch . '%'])
                            ->orWhereRaw('LOWER(send_numbers.err_code) LIKE ?', ['%' . $lowerSearch . '%'])
                            ->orWhereRaw('LOWER(error_code.error_status) LIKE ?', ['%' . $lowerSearch . '%'])
                            ->orWhere('send_numbers.msg_job_id', 'LIKE', '%' . $searchText . '%')
                            ->orWhere('send_numbers.master_job_id', 'LIKE', '%' . $searchText . '%')
                            ->orWhere('send_numbers.mobile_number', 'LIKE', '%' . $searchText . '%');
                    });
                }
            });
        }

        $countQuery = clone $baseQuery;
    
        $totalCount = $countQuery->distinct('send_numbers.id')->count('send_numbers.id');

        $query = clone $baseQuery;

        $query->distinct('send_numbers.id')->select([
            'send_numbers.id',
            'send_numbers.msg_job_id as job_id',
            'send_numbers.master_job_id as message_ID',
            'users.username',
            'sender_details.sender_ID',
            'send_numbers.send_msg as message',
            'send_messages.template_id',
            'send_numbers.mobile_number',
            'send_numbers.created_at as created_date',
            'send_numbers.sent_at as sent_date',
            'send_numbers.status',
            'send_numbers.err_code as error_code',
            'error_code.error_status as error_description',
            'send_numbers.route',
            'send_numbers.sent_at',
            DB::raw('TIME(send_numbers.delivered_date) as DLR'),
            'send_numbers.char_count as chars',
            'send_numbers.msgcredit as bill'
        ]);

        $sortColumn = match($sorting) {
            'created_at' => 'send_numbers.created_at',
            'sent_at' => 'send_numbers.sent_at',
            'delivered_date', 'DLR' => 'send_numbers.delivered_date',
            default => $sorting
        };

        if ($sorting === 'isActive') {
            $orderBy = strtolower($orderBy) === 'asc' ? 'desc' : 'asc';
        }

        $records = $query->orderBy($sortColumn, $orderBy)
                        ->skip($offset)
                        ->take($limit)
                        ->get();

        return [$records, $totalCount];
    }

    public static function getSendJobBasicDetails($request, $user)
    {
        $job_id = $request->job_id;

        // First, get unique send_numbers records with aggregation
        $sendNumbersSubquery = DB::table('send_numbers')
            ->where('msg_job_id', $job_id)
            ->whereNull('deleted_at')
            ->where('isActive', 1)
            ->select(
                'msg_job_id',
                'message_id',
                'senderid',
                DB::raw('MIN(created_at) as min_created_at'),
                DB::raw('MAX(send_msg) as max_send_msg'),
                DB::raw('SUM(msgcredit) as total_msgcredit')
            )
            ->groupBy('msg_job_id', 'message_id', 'senderid');

        // Then join with other tables
        $send_job_report = DB::table(DB::raw("({$sendNumbersSubquery->toSql()}) as send_numbers_agg"))
            ->mergeBindings($sendNumbersSubquery)
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers_agg.message_id')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers_agg.senderid')
            ->leftJoin('template_details', 'template_details.template_ID', '=', 'send_messages.template_id')
            ->select(
                'send_numbers_agg.msg_job_id as job_id',
                'send_messages.campaign_name',
                'sender_details.sender_ID',
                'send_messages.ip_address',
                'send_numbers_agg.min_created_at as timestamp',
                'send_numbers_agg.max_send_msg as text_data',
                'send_numbers_agg.total_msgcredit as total_bill',
                'send_messages.method',
                'send_messages.form_type as sent_through',
                'template_details.character_type as text_type'
            )
            ->first();

        if ($send_job_report) {
            // Get statuses with their msgcredit sums for this job_id
            $statuses = DB::table('send_numbers')
                ->where('msg_job_id', $job_id)
                ->whereNull('deleted_at')
                ->where('isActive', 1)
                ->groupBy('status')
                ->select(
                    'status', 
                    DB::raw('SUM(msgcredit) as sum')
                )
                ->get()
                ->keyBy('status')
                ->map(function($item) {
                    return (string)$item->sum;
                });

            $send_job_report->statuses = $statuses;
        }

        return $send_job_report;
    }

    public static function generateSendJobsSmsDownload($user_id, $status = null, $job_id = null, $startDate = null, $endDate = null)
    {
        $query = DB::table('send_numbers')
            ->leftJoin('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1)
            ->where('send_numbers.userids', $user_id);

        // Apply status filter
        if ($status && $status !== 'All') {
            if ($status === 'delivered') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'delivered')
                    ->orWhere('send_numbers.err_code', '000');
                });
            } elseif ($status === 'failed') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'failed')
                    ->orWhere(function ($q2) {
                        $q2->whereNotNull('send_numbers.err_code')
                            ->where('send_numbers.err_code', '!=', '000');
                    });
                });
            } elseif ($status === 'pending') {
                $query->where(function ($q) {
                    $q->whereNotIn('send_numbers.status', ['delivered', 'failed'])
                    ->where(function ($q2) {
                        $q2->whereNull('send_numbers.err_code')
                            ->orWhere('send_numbers.err_code', '');
                    });
                });
            } else {
                $query->where('send_numbers.status', $status);
            }
        }

        // Apply job_id filter
        if ($job_id) {
            $query->where('send_numbers.msg_job_id', $job_id);
        }

        // Date range filter - only apply if dates are provided
        if ($startDate) {
            $query->whereDate('send_numbers.created_at', '>=', $startDate);
        }
        if ($endDate) {
            $query->whereDate('send_numbers.created_at', '<=', $endDate);
        }

        $records = $query->select(
                'send_numbers.msg_job_id as job_id',
                'users.username as user',
                'sender_details.sender_ID as sender_id',
                'send_numbers.send_msg as message',
                'send_messages.template_id as template_id',
                'send_numbers.mobile_number',
                'send_numbers.created_at as created_date',
                'send_numbers.sent_at as sent_date',
                'send_numbers.status',
                'send_numbers.err_code as error_code',
                'error_code.error_status as error_description'
            )
            ->orderBy('send_numbers.created_at', 'asc')
            ->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000;
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "SendJobs_SMS_Report_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            // Use the new export class
            Excel::store(new \App\Exports\SendJobsSmsReportExport($chunk), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        $zipFileName = 'SendJobs_SMS_Reports_' . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }

    public static function getCampaignSmsSummary($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit = $params['limit'];
        $offset = $params['offset'];
        $searchText = $params['searchText'];
        $sorting = $params['sorting'];
        $orderBy = $params['orderBy'];

        $user_ID = $filters['user_ID'] ?? null;
        $role_ID = $filters['role_ID'] ?? null;
        $startDate = $filters['from_date'] ?? null;
        $endDate = $filters['to_date'] ?? null;

        $query = DB::table('send_numbers')
            ->join('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1);

        if ($startDate) {
            $query->whereDate('send_numbers.created_at', '>=', $startDate);
        }
        if ($endDate) {
            $query->whereDate('send_numbers.created_at', '<=', $endDate);
        }

        $query->when($user_ID && $user_ID !== 'All', function ($query) use ($user_ID) {
                $query->where('send_numbers.userids', $user_ID);
            })
            ->when($role_ID && $role_ID !== 'All', function ($query) use ($role_ID) {
                $query->where('users.role_id', $role_ID);
            })
            ->when(empty($user_ID) && empty($role_ID), function ($query) use ($user) {
                $query->where('send_numbers.userids', $user->id);
            })

            ->when($searchText, function ($query) use ($searchText) {
                $query->where(function ($q) use ($searchText) {
                    $lowerSearch = strtolower($searchText);

                    if (in_array($lowerSearch, ['active', 'inactive'])) {
                        $status = $lowerSearch === 'active' ? 1 : 0;
                        $q->where('send_numbers.isActive', $status);
                    } else {
                        $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(send_messages.campaign_name) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(send_numbers.msg_job_id) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('send_numbers.created_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                    }
                });
            })
            ->select(
                'users.username',
                'send_numbers.userids as user_ID',
                'send_messages.campaign_name',
                'send_numbers.msg_job_id as job_id',
                DB::raw('COUNT(*) as total'),
                DB::raw('COUNT(*) as submitted'),
                DB::raw("COUNT(CASE WHEN send_numbers.status = 'delivered' OR send_numbers.err_code = '000' THEN 1 ELSE NULL END) as delivered"),
                DB::raw("COUNT(CASE WHEN send_numbers.status = 'failed' OR (send_numbers.err_code IS NOT NULL AND send_numbers.err_code != '000') THEN 1 ELSE NULL END) as failed"),
                // DB::raw("COUNT(CASE WHEN send_numbers.status NOT IN ('delivered', 'failed', 'submitted') AND (send_numbers.err_code IS NULL OR send_numbers.err_code = '') THEN 1 ELSE NULL END) as pending")
                DB::raw("COUNT(CASE WHEN send_numbers.status = 'submitted' THEN 1 ELSE NULL END) as pending"),
            )
            ->groupBy('users.username', 'send_numbers.userids', 'send_messages.campaign_name', 'send_numbers.msg_job_id');

        // Get total count
        $totalCount = (clone $query)->get()->count();

        // Apply sorting and pagination
        $allowedSortColumns = ['username', 'campaign_name', 'job_id', 'total', 'submitted', 'delivered', 'failed', 'pending'];
        
        if (in_array($sorting, $allowedSortColumns)) {
            $query->orderBy($sorting, $orderBy);
        } else {
            $query->orderBy('total', 'desc');
        }

        $records = $query->skip($offset)->take($limit)->get();

        $formattedRecords = $records->map(function ($record) {
            return [
                'username' => $record->username,
                'user_ID' => $record->user_ID,
                'campaign_name' => $record->campaign_name,
                'job_id' => $record->job_id,
                'total' => $record->total,
                'submitted' => $record->submitted,
                'delivered' => $record->delivered,
                'failed' => $record->failed,
                'pending' => $record->pending,
            ];
        });

        return [['records' => $formattedRecords->toArray()], $totalCount];
    }

    public static function getCampaignSmsReport($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit = $params['limit'];
        $offset = $params['offset'];
        $searchText = $params['searchText'];
        $sorting = $params['sorting'];
        $orderBy = $params['orderBy'];

        $user_ID = $filters['user_ID'] ?? null;
        $role_ID = $filters['role_ID'] ?? null;
        $startDate = $filters['from_date'] ?? null;
        $endDate = $filters['to_date'] ?? null;
        $status = $filters['status'] ?? null;
        $campaign_name = $filters['campaign_name'] ?? null;
        $job_id = $filters['job_id'] ?? null;

        $query = DB::table('send_numbers')
            ->leftJoin('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1);

        if ($startDate) {
            $query->whereDate('send_numbers.created_at', '>=', $startDate);
        }
        if ($endDate) {
            $query->whereDate('send_numbers.created_at', '<=', $endDate);
        }

        $query->when($user_ID && $user_ID !== 'All', function ($query) use ($user_ID) {
                $query->where('send_numbers.userids', $user_ID);
            })
            ->when($role_ID && $role_ID !== 'All', function ($query) use ($role_ID) {
                $query->where('users.role_id', $role_ID);
            })
            ->when($campaign_name && $campaign_name !== 'All', function ($query) use ($campaign_name) {
                $query->where('send_messages.campaign_name', $campaign_name);
            })
            ->when($job_id, function ($query) use ($job_id) {
                $query->where('send_numbers.msg_job_id', $job_id);
            })
            ->when($status && $status !== 'All', function ($query) use ($status) {
                if ($status === 'delivered') {
                    $query->where(function ($q) {
                        $q->where('send_numbers.status', 'delivered')
                        ->orWhere('send_numbers.err_code', '000');
                    });
                } elseif ($status === 'failed') {
                    $query->where(function ($q) {
                        $q->where('send_numbers.status', 'failed')
                        ->orWhere(function ($q2) {
                            $q2->whereNotNull('send_numbers.err_code')
                                ->where('send_numbers.err_code', '!=', '000');
                        });
                    });
                } elseif ($status === 'pending') {
                    $query->where(function ($q) {
                        $q->whereNotIn('send_numbers.status', ['delivered', 'failed'])
                        ->where(function ($q2) {
                            $q2->whereNull('send_numbers.err_code')
                                ->orWhere('send_numbers.err_code', '');
                        });
                    });
                } else {
                    $query->where('send_numbers.status', $status);
                }
            })

            ->when($searchText, function ($query) use ($searchText) {
                $query->where(function ($q) use ($searchText) {
                    $lowerSearch = strtolower($searchText);

                    if (in_array($lowerSearch, ['active', 'inactive'])) {
                        $status = $lowerSearch === 'active' ? 1 : 0;
                        $q->where('send_numbers.isActive', $status);
                    } else {
                        $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(send_messages.campaign_name) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(sender_details.sender_ID) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(send_messages.template_id) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(send_numbers.status) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(send_numbers.err_code) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(error_code.error_status) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('send_numbers.msg_job_id LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('send_numbers.mobile_number LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('send_numbers.send_msg LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('send_numbers.created_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('send_numbers.sent_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.sent_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.sent_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                    }
                });
            })
            ->select(
                'send_numbers.msg_job_id as job_id',
                'users.username',
                'send_messages.campaign_name',
                'sender_details.sender_ID',
                'send_numbers.send_msg as message',
                'send_messages.template_id as template_id',
                'send_numbers.mobile_number',
                'send_numbers.created_at as created_date',
                'send_numbers.sent_at as sent_date',
                'send_numbers.status',
                'send_numbers.err_code as error_code',
                'error_code.error_status as error_description'
            );

        // Get total count
        $totalCount = (clone $query)->count();

        // Apply sorting and pagination
        if ($sorting === 'isActive') {
            $orderBy = strtolower($orderBy) === 'asc' ? 'desc' : 'asc';
        }

        // Handle ambiguous column names in sorting
        $sortColumn = $sorting;
        if ($sorting === 'created_at') {
            $sortColumn = 'send_numbers.created_at';
        } elseif ($sorting === 'sent_at') {
            $sortColumn = 'send_numbers.sent_at';
        }

        $records = $query->orderBy($sortColumn, $orderBy)
                        ->skip($offset)
                        ->take($limit)
                        ->get();

        return [$records, $totalCount];
    }

    public static function generateCampaignSmsDownload($user_id, $status = null, $campaign_name = null, $job_id = null, $startDate = null, $endDate = null)
    {
        $query = DB::table('send_numbers')
            ->leftJoin('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1)
            ->where('send_numbers.userids', $user_id);

        // Apply status filter
        if ($status && $status !== 'All') {
            if ($status === 'delivered') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'delivered')
                    ->orWhere('send_numbers.err_code', '000');
                });
            } elseif ($status === 'failed') {
                $query->where(function ($q) {
                    $q->where('send_numbers.status', 'failed')
                    ->orWhere(function ($q2) {
                        $q2->whereNotNull('send_numbers.err_code')
                            ->where('send_numbers.err_code', '!=', '000');
                    });
                });
            } elseif ($status === 'pending') {
                $query->where(function ($q) {
                    $q->whereNotIn('send_numbers.status', ['delivered', 'failed'])
                    ->where(function ($q2) {
                        $q2->whereNull('send_numbers.err_code')
                            ->orWhere('send_numbers.err_code', '');
                    });
                });
            } else {
                $query->where('send_numbers.status', $status);
            }
        }

        // Apply campaign_name filter
        if ($campaign_name && $campaign_name !== 'All') {
            $query->where('send_messages.campaign_name', $campaign_name);
        }

        // Apply job_id filter
        if ($job_id) {
            $query->where('send_numbers.msg_job_id', $job_id);
        }

        // Apply date range
        if ($startDate) {
            $query->whereDate('send_numbers.created_at', '>=', $startDate);
        }
        if ($endDate) {
            $query->whereDate('send_numbers.created_at', '<=', $endDate);
        }

        $records = $query->select(
                'send_numbers.msg_job_id as job_id',
                'users.username as user',
                'send_messages.campaign_name',
                'sender_details.sender_ID as sender_id',
                'send_numbers.send_msg as message',
                'send_messages.template_id as template_id',
                'send_numbers.mobile_number',
                'send_numbers.created_at as created_date',
                'send_numbers.sent_at as sent_date',
                'send_numbers.status',
                'send_numbers.err_code as error_code',
                'error_code.error_status as error_description'
            )
            ->orderBy('send_numbers.created_at', 'asc')
            ->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000;
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "Campaign_SMS_Report_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            Excel::store(new \App\Exports\CampaignSmsReportExport($chunk), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        $zipFileName = 'Campaign_SMS_Reports_' . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }

    public static function getSmsDownloadCenter($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit          = $params['limit'];
        $offset         = $params['offset'];
        $searchText     = $params['searchText'];
        $sorting        = $params['sorting'];
        $orderBy        = $params['orderBy'];
        
        $user_ID        = $filters['user_ID'] ?? null;
        $role_ID        = $filters['role_ID'] ?? null;
        $sender_ID      = $filters['sender_ID'] ?? null;
        $startDate      = $filters['from_date'] ?? null;
        $endDate        = $filters['to_date'] ?? null;
        $mobile_number  = $filters['mobile_number'] ?? null;

        $query = DB::table('send_numbers')
            ->join('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1)

            ->when($user_ID && $user_ID !== 'All', function ($query) use ($user_ID) {
                $query->where('send_numbers.userids', $user_ID);
            })
            ->when($role_ID && $role_ID !== 'All', function ($query) use ($role_ID) {
                $query->where('users.role_id', $role_ID);
            })
            ->when($sender_ID && $sender_ID !== 'All', function ($query) use ($sender_ID) {
                $query->where('send_numbers.senderid', $sender_ID);
            })
            ->when(empty($user_ID) && empty($role_ID), function ($query) use ($user) {
                $query->where('send_numbers.userids', $user->id);
            })
            ->when($startDate, function ($query) use ($startDate) {
                $query->whereDate('send_numbers.created_at', '>=', $startDate);
            })
            ->when($endDate, function ($query) use ($endDate) {
                $query->whereDate('send_numbers.created_at', '<=', $endDate);
            })
            ->when($mobile_number, function ($query) use ($mobile_number) {
                $query->where('send_numbers.mobile_number', '=', $mobile_number);
            })

            ->when($searchText, function ($query) use ($searchText) {
                $query->where(function ($q) use ($searchText) {
                    $lowerSearch = strtolower($searchText);

                    if (in_array($lowerSearch, ['active', 'inactive'])) {
                        $status = $lowerSearch === 'active' ? 1 : 0;
                        $q->where('send_numbers.isActive', $status);
                    } else {
                        $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(sender_details.sender_ID) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('send_numbers.mobile_number LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('send_numbers.msgcredit LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('send_numbers.created_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                    }
                });
            })
            ->select(
                'users.username',
                'send_numbers.userids as user_ID', 
                DB::raw('SUM(send_numbers.msgcredit) as bill_credit')
            )
            ->groupBy('users.username', 'send_numbers.userids');

        // Get total count
        $totalCount = (clone $query)->count();

        // Apply sorting and pagination
        $allowedSortColumns = ['username', 'bill_credit', 'user_ID'];
        
        if (in_array($sorting, $allowedSortColumns)) {
            $query->orderBy($sorting, $orderBy);
        } else {
            $query->orderBy('bill_credit', 'desc');
        }

        $sms_download_center = $query
            ->skip($offset)
            ->take($limit)
            ->get();

        return [$sms_download_center, $totalCount];
    }

    public static function generateSmsDownloadCenter($user_id, $sender_id = null, $mobile_number = null, $startDate = null, $endDate = null)
    {
        $query = DB::table('send_numbers')
            ->leftJoin('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->leftJoin('send_messages', 'send_messages.id', '=', 'send_numbers.message_id')
            ->leftJoin('error_code', 'error_code.error_code', '=', 'send_numbers.err_code')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1)
            ->where('send_numbers.userids', $user_id);

        // Apply sender_id filter
        if ($sender_id && $sender_id !== 'All') {
            $query->where('send_numbers.senderid', $sender_id);
        }

        // Apply mobile_number filter
        if ($mobile_number) {
            $query->where('send_numbers.mobile_number', $mobile_number);
        }

        // Apply date range
        if (!empty($startDate) && !empty($endDate)) {
            $query->whereBetween('send_numbers.created_at', [$startDate, $endDate]);
        }

        $records = $query->select(
                'send_numbers.msg_job_id as job_id',
                'users.username as user',
                'sender_details.sender_ID as sender_id',
                'send_numbers.send_msg as message',
                'send_messages.template_id as template_id',
                'send_numbers.mobile_number',
                'send_numbers.created_at as created_date',
                'send_numbers.sent_at as sent_date',
                'send_numbers.status',
                'send_numbers.err_code as error_code',
                'error_code.error_status as error_description'
            )
            ->orderBy('send_numbers.created_at', 'asc')
            ->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000; // 4 lakh rows per file
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "SMS_Report_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            Excel::store(new \App\Exports\SmsDownloadExport($chunk), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        // If only one file → return directly
        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        // If multiple files → create ZIP
        $zipFileName = 'SMS_Reports_' . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        // Clean up individual files
        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }
    
    public static function getSmsMisReport($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit = $params['limit'];
        $offset = $params['offset'];
        $searchText = $params['searchText'];
        $sorting = $params['sorting'];
        $orderBy = $params['orderBy'];

        $user_ID = $filters['user_ID'] ?? null;
        $role_ID = $filters['role_ID'] ?? null;
        $sender_ID = $filters['sender_ID'] ?? null;
        $startDate = $filters['start_date'] ?? $filters['from_date'] ?? null;
        $endDate = $filters['end_date'] ?? $filters['to_date'] ?? null;
        $month = $filters['month'] ?? null;
        $year = $filters['year'] ?? null;
        $type = $filters['type'] ?? 'daily';

        // Build datetime boundaries so today's records (created_at with a time
        // component) are not excluded. A bare date as the BETWEEN upper bound is
        // treated as 00:00:00, dropping everything created later that same day.
        $startDateTime = $startDate ? date('Y-m-d 00:00:00', strtotime($startDate)) : null;
        $endDateTime = $endDate ? date('Y-m-d 23:59:59', strtotime($endDate)) : null;

        // Convert month name to number if provided as string
        $monthNumber = null;
        if ($month) {
            if (is_numeric($month)) {
                $monthNumber = (int) $month;
            } else {
                $monthNumber = date('n', strtotime($month));
            }
        }

        $query = DB::table('send_numbers')
            ->join('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1);

        // Apply user filter
        if ($user_ID && $user_ID !== 'All') {
            $query->where('send_numbers.userids', $user_ID);
        }

        // Apply role filter
        if ($role_ID && $role_ID !== 'All') {
            $query->where('users.role_id', $role_ID);
        }

        // Apply sender ID filter for sender_wise type
        if ($type === 'sender_wise' && $sender_ID && $sender_ID !== 'All') {
            $query->where('send_numbers.senderid', $sender_ID);
        }

        // Apply date filters based on type
        switch ($type) {
            case 'daily':
                if ($startDate && $endDate) {
                    $query->whereBetween('send_numbers.created_at', [$startDate, $endDate]);
                } else {
                    $query->whereDate('send_numbers.created_at', today());
                }
                break;

            case 'monthly':
                if ($month && $year) {
                    $query->whereYear('send_numbers.created_at', $year)
                        ->whereMonth('send_numbers.created_at', $monthNumber);
                } else {
                    $query->whereYear('send_numbers.created_at', date('Y'))
                        ->whereMonth('send_numbers.created_at', date('m'));
                }
                break;

            case 'yearly':
                $query->whereYear('send_numbers.created_at', $year ?: date('Y'));
                break;

            case 'customised':
                if ($startDate && $endDate) {
                    $query->whereBetween('send_numbers.created_at', [$startDateTime, $endDateTime]);
                } else {
                    // No date range selected: show no data until filters are applied
                    $query->whereRaw('1 = 0');
                }
                break;

            case 'sender_wise':
                // No specific date filter for sender wise
                break;
        }

        // Search functionality
        if ($searchText) {
            $query->where(function ($q) use ($searchText) {
                $lowerSearch = strtolower($searchText);

                if (in_array($lowerSearch, ['active', 'inactive'])) {
                    $status = $lowerSearch === 'active' ? 1 : 0;
                    $q->where('send_numbers.isActive', $status);
                } else {
                    $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(sender_details.sender_ID) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('send_numbers.created_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(send_numbers.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                }
            });
        }

        // Grouping based on type
        switch ($type) {
            case 'daily':
                $groupBy = DB::raw('DATE(send_numbers.created_at)');
                $selectGroup = DB::raw('DATE(send_numbers.created_at) as period');
                break;

            case 'monthly':
                $groupBy = DB::raw("DATE_FORMAT(send_numbers.created_at, '%M %Y')");
                $selectGroup = DB::raw("DATE_FORMAT(send_numbers.created_at, '%M %Y') as period");
                break;

            case 'yearly':
                $groupBy = DB::raw("YEAR(send_numbers.created_at)");
                $selectGroup = DB::raw("YEAR(send_numbers.created_at) as period");
                break;

            case 'sender_wise':
                $groupBy = 'sender_details.sender_ID';
                $selectGroup = 'sender_details.sender_ID as period';
                break;

            case 'customised':
                $groupBy = DB::raw('DATE(send_numbers.created_at)');
                $selectGroup = DB::raw('DATE(send_numbers.created_at) as period');
                break;

            default:
                throw new \Exception("Invalid type");
        }

        $summaryQuery = $query->select(
            $selectGroup,
            'users.username',
            'send_numbers.userids as user_ID',
            DB::raw('MAX(sender_details.sender_ID) as sender_name'),
            DB::raw('COUNT(*) as total'),
            DB::raw('COUNT(*) as submitted'),
            DB::raw("COUNT(CASE WHEN send_numbers.status = 'delivered' OR send_numbers.err_code = '000' THEN 1 END) as delivered"),
            DB::raw("COUNT(CASE WHEN send_numbers.status = 'failed' OR (send_numbers.err_code IS NOT NULL AND send_numbers.err_code != '000') THEN 1 END) as failed"),
            // DB::raw("COUNT(CASE WHEN send_numbers.status NOT IN ('delivered', 'failed') AND (send_numbers.err_code IS NULL OR send_numbers.err_code = '') THEN 1 END) as pending")
            DB::raw("COUNT(CASE WHEN send_numbers.status = 'submitted' THEN 1 ELSE NULL END) as pending"),
        );

        if ($type === 'sender_wise') {
            $summaryQuery->groupBy('sender_details.sender_ID', 'users.username', 'send_numbers.userids');
        } else {
            $summaryQuery->groupBy($groupBy, 'users.username', 'send_numbers.userids');
        }

        // Get total count
        $totalCount = (clone $query)->get()->count();

        // Apply sorting and pagination
        $allowedSortColumns = ['period', 'username', 'user_ID', 'total', 'submitted', 'delivered', 'failed', 'pending'];
        if (in_array($sorting, $allowedSortColumns)) {
            $summaryQuery->orderBy($sorting, $orderBy);
        } else {
            $summaryQuery->orderBy('total', 'desc');
        }

        $records = $summaryQuery->skip($offset)->take($limit)->get();

        // Final Formatting with conditional key removal
        $formattedRecords = $records->map(function ($record) use ($type, $sender_ID, $month, $year, $startDate, $endDate) {
            $data = [
                'period' => $record->period,
                'username' => $record->username,
                'user_ID' => $record->user_ID,
                'sender_name' => $record->sender_name,
                'total' => $record->total,
                'submitted' => $record->submitted,
                'delivered' => $record->delivered,
                'failed' => $record->failed,
                'pending' => $record->pending,
                'type' => $type,
                'sender_ID' => $type === 'sender_wise' ? ($sender_ID ?: '') : '',
                'month' => $type === 'monthly' ? ($month ?: date('M')) : null,
                'year' => in_array($type, ['monthly', 'yearly']) ? ($year ?: date('Y')) : '',
                'start_date' => in_array($type, ['daily', 'customised']) ? ($startDate ?: '') : '',
                'end_date' => in_array($type, ['daily', 'customised']) ? ($endDate ?: '') : ''
            ];

            // Remove unwanted keys based on type
            switch ($type) {
                case 'daily':
                    unset($data['month'], $data['year'], $data['sender_ID'], $data['sender_name']);
                    break;
                case 'monthly':
                    unset($data['period'], $data['year'], $data['sender_ID'], $data['sender_name']);
                    break;
                case 'sender_wise':
                    unset($data['period'], $data['year'], $data['month']);
                    break;
                case 'yearly':
                    unset($data['month'], $data['period'], $data['sender_ID'], $data['sender_name']);
                    break;
                case 'customised':
                    unset($data['month'], $data['year'], $data['sender_ID'], $data['sender_name']);
                    break;
            }

            return $data;
        });

        return [$formattedRecords, $totalCount];
    }


    public static function generateSmsMisDownload($type = 'daily', $user_ID = null, $role_ID = null, $sender_ID = null, $startDate = null, $endDate = null, $month = null, $year = null)
    {
        $query = DB::table('send_numbers')
            ->join('users', 'users.id', '=', 'send_numbers.userids')
            ->leftJoin('sender_details', 'sender_details.id', '=', 'send_numbers.senderid')
            ->whereNull('send_numbers.deleted_at')
            ->where('send_numbers.isActive', 1);

        // Apply user filter
        if ($user_ID && $user_ID !== 'All') {
            $query->where('send_numbers.userids', $user_ID);
        }

        // Apply role filter
        if ($role_ID && $role_ID !== 'All') {
            $query->where('users.role_id', $role_ID);
        }

        // Apply sender ID filter for sender_wise type
        if ($type === 'sender_wise' && $sender_ID && $sender_ID !== 'All') {
            $query->where('send_numbers.senderid', $sender_ID);
        }

        // Build datetime boundaries so today's records (created_at with a time
        // component) are not excluded by a bare-date BETWEEN upper bound.
        $startDateTime = $startDate ? date('Y-m-d 00:00:00', strtotime($startDate)) : null;
        $endDateTime = $endDate ? date('Y-m-d 23:59:59', strtotime($endDate)) : null;

        // Apply date filters based on type
        switch ($type) {
            case 'daily':
                if ($startDate && $endDate) {
                    $query->whereBetween('send_numbers.created_at', [$startDateTime, $endDateTime]);
                } else {
                    $query->whereDate('send_numbers.created_at', today());
                }
                break;

            case 'monthly':
                if ($month && $year) {
                    $query->whereYear('send_numbers.created_at', $year)
                        ->whereMonth('send_numbers.created_at', $month);
                } else {
                    $query->whereYear('send_numbers.created_at', date('Y'))
                        ->whereMonth('send_numbers.created_at', date('m'));
                }
                break;

            case 'yearly':
                if ($year) {
                    $query->whereYear('send_numbers.created_at', $year);
                } else {
                    $query->whereYear('send_numbers.created_at', date('Y'));
                }
                break;

            case 'customised':
                if ($startDate && $endDate) {
                    $query->whereBetween('send_numbers.created_at', [$startDateTime, $endDateTime]);
                }
                break;
                
            case 'sender_wise':
                // No specific date filter for sender wise
                break;
        }

        // Grouping based on type
        switch ($type) {
            case 'daily':
                $groupBy = DB::raw('DATE(send_numbers.created_at)');
                $selectGroup = DB::raw('DATE(send_numbers.created_at) as period');
                break;
                
            case 'monthly':
                $groupBy = DB::raw("DATE_FORMAT(send_numbers.created_at, '%M %Y')");
                $selectGroup = DB::raw("DATE_FORMAT(send_numbers.created_at, '%M %Y') as period");
                break;
                
            case 'yearly':
                $groupBy = DB::raw("YEAR(send_numbers.created_at)");
                $selectGroup = DB::raw("YEAR(send_numbers.created_at) as period");
                break;
                
            case 'sender_wise':
                $groupBy = 'sender_details.sender_ID';
                $selectGroup = 'sender_details.sender_ID as period';
                break;
                
            case 'customised':
                $groupBy = DB::raw('DATE(send_numbers.created_at)');
                $selectGroup = DB::raw('DATE(send_numbers.created_at) as period');
                break;
                
            default:
                throw new \Exception("Invalid type");
        }

        $records = $query->select(
            $selectGroup,
            'users.username',
            DB::raw('COUNT(*) as total'),
            DB::raw('COUNT(*) as submitted'),
            DB::raw("COUNT(CASE WHEN send_numbers.status = 'delivered' OR send_numbers.err_code = '000' THEN 1 ELSE NULL END) as delivered"),
            DB::raw("COUNT(CASE WHEN send_numbers.status = 'failed' OR (send_numbers.err_code IS NOT NULL AND send_numbers.err_code != '000') THEN 1 ELSE NULL END) as failed"),
            DB::raw("COUNT(CASE WHEN send_numbers.status NOT IN ('delivered', 'failed') AND (send_numbers.err_code IS NULL OR send_numbers.err_code = '') THEN 1 ELSE NULL END) as pending")
        );

        // Add group by based on type
        if ($type === 'sender_wise') {
            $records->groupBy('sender_details.sender_ID', 'users.username');
        } else {
            $records->groupBy($groupBy, 'users.username');
        }

        $records = $records->orderBy('period', 'asc')->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000;
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "SMS_MIS_Report_{$type}_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            Excel::store(new \App\Exports\SmsMisReportExport($chunk, $type), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        $zipFileName = "SMS_MIS_Reports_{$type}_" . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }

    public static function getUrlTrackingSummary($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit = $params['limit'];
        $offset = $params['offset'];
        $searchText = $params['searchText'];
        $sorting = $params['sorting'];
        $orderBy = $params['orderBy'];

        $user_ID = $filters['user_ID'] ?? null;
        $role_ID = $filters['role_ID'] ?? null;
        $startDate = $filters['from_date'] ?? null;
        $endDate = $filters['to_date'] ?? null;

        $query = DB::table('tracking_url')
            ->join('users', 'users.id', '=', 'tracking_url.created_by')
            ->whereNull('tracking_url.deleted_at')
            ->where('tracking_url.isActive', 1)

            ->when($user_ID && $user_ID !== 'All', function ($query) use ($user_ID) {
                $query->where('tracking_url.created_by', $user_ID);
            })
            ->when($role_ID && $role_ID !== 'All', function ($query) use ($role_ID) {
                $query->where('users.role_id', $role_ID);
            })
            ->when(empty($user_ID) && empty($role_ID), function ($query) use ($user) {
                $query->where('tracking_url.created_by', $user->id);
            })
            ->when($startDate, function ($query) use ($startDate) {
                $query->whereDate('tracking_url.created_at', '>=', $startDate);
            })
            ->when($endDate, function ($query) use ($endDate) {
                $query->whereDate('tracking_url.created_at', '<=', $endDate);
            })

            ->when($searchText, function ($query) use ($searchText) {
                $query->where(function ($q) use ($searchText) {
                    $lowerSearch = strtolower($searchText);

                    if (in_array($lowerSearch, ['active', 'inactive'])) {
                        $status = $lowerSearch === 'active' ? 1 : 0;
                        $q->where('tracking_url.isActive', $status);
                    } else {
                        $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(tracking_url.job_id) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(tracking_url.campaign_name) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('tracking_url.click_count LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('tracking_url.created_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(tracking_url.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(tracking_url.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                    }
                });
            })
            ->select(
                'users.username',
                'tracking_url.created_by as user_ID',
                'tracking_url.job_id',
                'tracking_url.campaign_name',
                'tracking_url.created_at as created_date',
                DB::raw('SUM(tracking_url.click_count) as click_count')
            )
            ->groupBy('users.username', 'tracking_url.created_by', 'tracking_url.job_id', 'tracking_url.campaign_name', 'tracking_url.created_at');

        // Get total count
        $totalCount = (clone $query)->get()->count();

        // Apply sorting and pagination
        $allowedSortColumns = ['user', 'job_id', 'campaign_name', 'created_date', 'click_count'];
        
        if (in_array($sorting, $allowedSortColumns)) {
            $query->orderBy($sorting, $orderBy);
        } else {
            $query->orderBy('click_count', 'desc');
        }

        $records = $query->skip($offset)->take($limit)->get();

        return [$records, $totalCount];
    }

    public static function getUrlTrackingReport($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit = $params['limit'];
        $offset = $params['offset'];
        $searchText = $params['searchText'];
        $sorting = $params['sorting'];
        $orderBy = $params['orderBy'];

        $user_ID = $filters['user_ID'] ?? null;
        $role_ID = $filters['role_ID'] ?? null;
        $startDate = $filters['from_date'] ?? null;
        $endDate = $filters['to_date'] ?? null;
        $job_id = $filters['job_id'] ?? null;
        $campaign_name = $filters['campaign_name'] ?? null;

        $query = DB::table('tracking_url')
            ->join('users', 'users.id', '=', 'tracking_url.created_by')
            ->whereNull('tracking_url.deleted_at')
            ->where('tracking_url.isActive', 1)

            ->when($user_ID && $user_ID !== 'All', function ($query) use ($user_ID) {
                $query->where('tracking_url.created_by', $user_ID);
            })
            ->when($role_ID && $role_ID !== 'All', function ($query) use ($role_ID) {
                $query->where('users.role_id', $role_ID);
            })
            ->when($job_id, function ($query) use ($job_id) {
                $query->where('tracking_url.job_id', $job_id);
            })
            ->when($campaign_name, function ($query) use ($campaign_name) {
                $query->where('tracking_url.campaign_name', $campaign_name);
            })
            ->when($startDate, function ($query) use ($startDate) {
                $query->whereDate('tracking_url.created_at', '>=', $startDate);
            })
            ->when($endDate, function ($query) use ($endDate) {
                $query->whereDate('tracking_url.created_at', '<=', $endDate);
            })

            ->when($searchText, function ($query) use ($searchText) {
                $query->where(function ($q) use ($searchText) {
                    $lowerSearch = strtolower($searchText);

                    if (in_array($lowerSearch, ['active', 'inactive'])) {
                        $status = $lowerSearch === 'active' ? 1 : 0;
                        $q->where('tracking_url.isActive', $status);
                    } else {
                        $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(tracking_url.job_id) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(tracking_url.campaign_name) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(tracking_url.mobile_number) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(tracking_url.device_browser) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(tracking_url.ip_address) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(tracking_url.city) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('tracking_url.click_count LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('tracking_url.created_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(tracking_url.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(tracking_url.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                    }
                });
            })
            ->select(
                'users.username',
                'tracking_url.job_id',
                'tracking_url.campaign_name',
                'tracking_url.mobile_number',
                'tracking_url.device_browser',
                'tracking_url.ip_address',
                'tracking_url.city',
                'tracking_url.click_count',
                'tracking_url.created_at as click_date'
            );

        // Get total count
        $totalCount = (clone $query)->count();

        // Apply sorting and pagination
        if ($sorting === 'isActive') {
            $orderBy = strtolower($orderBy) === 'asc' ? 'desc' : 'asc';
        }

        // Handle ambiguous column names in sorting
        $sortColumn = $sorting;
        if ($sorting === 'created_at') {
            $sortColumn = 'tracking_url.created_at';
        }

        $records = $query->orderBy($sortColumn, $orderBy)
                        ->skip($offset)
                        ->take($limit)
                        ->get();

        return [$records, $totalCount];
    }

    public static function generateUrlTrackingDownload($user_id, $job_id = null, $campaign_name = null, $startDate = null, $endDate = null)
    {
        $query = DB::table('tracking_url')
            ->join('users', 'users.id', '=', 'tracking_url.created_by')
            ->whereNull('tracking_url.deleted_at')
            ->where('tracking_url.isActive', 1);

        if (!empty($user_id) && $user_id !== 'All') {
            $query->where('tracking_url.created_by', $user_id);
        }

        // Apply job_id filter
        if ($job_id) {
            $query->where('tracking_url.job_id', $job_id);
        }

        // Apply campaign_name filter
        if ($campaign_name && $campaign_name !== 'All') {
            $query->where('tracking_url.campaign_name', $campaign_name);
        }

        // Apply date range
        if (!empty($startDate) && !empty($endDate)) {
            $query->whereBetween('tracking_url.created_at', [$startDate, $endDate]);
        }

        $records = $query->select(
                'users.username as user',
                'tracking_url.job_id',
                'tracking_url.campaign_name',
                'tracking_url.mobile_number',
                'tracking_url.device_browser',
                'tracking_url.ip_address',
                'tracking_url.city',
                'tracking_url.click_count',
                'tracking_url.created_at as click_date'
            )
            ->orderBy('tracking_url.created_at', 'asc')
            ->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000;
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "URL_Tracking_Report_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            Excel::store(new \App\Exports\UrlTrackingReportExport($chunk), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        $zipFileName = 'URL_Tracking_Reports_' . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }

    public static function getMiscallSmsReports($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit          = $params['limit'];
        $offset         = $params['offset'];
        $searchText     = $params['searchText'];
        $sorting        = $params['sorting'];
        $orderBy        = $params['orderBy'];
        
        $user_ID        = $filters['user_ID'] ?? null;
        $role_ID        = $filters['role_ID'] ?? null;
        $startDate      = $filters['from_date'] ?? null;
        $endDate        = $filters['to_date'] ?? null;

        $query = DB::table('misscall_reports')
            ->join('users', 'users.id', '=', 'misscall_reports.user_ID')
            ->whereNull('misscall_reports.deleted_at')
            ->where('misscall_reports.isActive', 1)

            ->when($user_ID && $user_ID !== 'All', function ($query) use ($user_ID) {
                $query->where('misscall_reports.user_ID', $user_ID);
            })
            ->when($role_ID && $role_ID !== 'All', function ($query) use ($role_ID) {
                $query->where('users.role_id', $role_ID);
            })
            ->when(empty($user_ID) && empty($role_ID), function ($query) use ($user) {
                $query->where('misscall_reports.user_ID', $user->id);
            })
            ->when($startDate, function ($query) use ($startDate) {
                $query->whereDate('misscall_reports.created_at', '>=', $startDate);
            })
            ->when($endDate, function ($query) use ($endDate) {
                $query->whereDate('misscall_reports.created_at', '<=', $endDate);
            })

            ->when($searchText, function ($query) use ($searchText) {
                $query->where(function ($q) use ($searchText) {
                    $lowerSearch = strtolower($searchText);

                    if (in_array($lowerSearch, ['active', 'inactive'])) {
                        $status = $lowerSearch === 'active' ? 1 : 0;
                        $q->where('misscall_reports.isActive', $status);
                    } else {
                        $q->whereRaw('LOWER(users.username) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(misscall_reports.misscall_number) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(misscall_reports.dailer_number) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(misscall_reports.status) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('misscall_reports.ivr_duration LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('misscall_reports.created_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw('misscall_reports.call_date LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(misscall_reports.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(misscall_reports.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(misscall_reports.call_date, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(misscall_reports.call_date, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                    }
                });
            })
            ->select(
                'misscall_reports.created_at as date',
                'users.username as user',
                'misscall_reports.misscall_number',
                'misscall_reports.dailer_number',
                'misscall_reports.status',
                'misscall_reports.call_date',
                'misscall_reports.ivr_duration as call_duration'
            );

        // Get total count
        $totalCount = (clone $query)->count();

        // Apply sorting and pagination
        $allowedSortColumns = ['date', 'user', 'misscall_number', 'dailer_number', 'status', 'call_date', 'call_duration'];
        
        if (in_array($sorting, $allowedSortColumns)) {
            $query->orderBy($sorting, $orderBy);
        } else {
            $query->orderBy('misscall_reports.created_at', 'desc');
        }

        $misscall_reportss = $query
            ->skip($offset)
            ->take($limit)
            ->get();

        return [$misscall_reportss, $totalCount];
    }

    public static function generateMiscallSmsReportsDownload($user_ID = null, $role_ID = null, $startDate = null, $endDate = null)
    {
        $query = DB::table('misscall_reports')
            ->leftJoin('users', 'users.id', '=', 'misscall_reports.user_ID')
            ->whereNull('misscall_reports.deleted_at')
            ->where('misscall_reports.isActive', 1);

        // Apply user filter
        if ($user_ID && $user_ID !== 'All') {
            $query->where('misscall_reports.user_ID', $user_ID);
        }

        // Apply role filter
        if ($role_ID && $role_ID !== 'All') {
            $query->where('users.role_id', $role_ID);
        }

        // Apply date range
        if (!empty($startDate) && !empty($endDate)) {
            $query->whereBetween('misscall_reports.created_at', [$startDate, $endDate]);
        }

        $records = $query->select(
                'misscall_reports.created_at as date',
                'users.username as user',
                'misscall_reports.misscall_number',
                'misscall_reports.dailer_number',
                'misscall_reports.status',
                'misscall_reports.call_date',
                'misscall_reports.ivr_duration as call_duration'
            )
            ->orderBy('misscall_reports.created_at', 'asc')
            ->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000; // 4 lakh rows per file
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "Miscall_SMS_Reports_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            Excel::store(new \App\Exports\MiscallSmsReportsExport($chunk), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        // If only one file → return directly
        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        // If multiple files → create ZIP
        $zipFileName = 'Miscall_SMS_Reports_' . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        // Clean up individual files
        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }

    public static function getErrorDescriptionReports($user, array $filters)
    {
        $params = getListingParams($filters);

        $limit          = $params['limit'];
        $offset         = $params['offset'];
        $searchText     = $params['searchText'];
        $sorting        = $params['sorting'];
        $orderBy        = $params['orderBy'];
        
        $gateway_ID     = $filters['gateway_ID'] ?? null;

        $query = DB::table('error_code')
            ->leftJoin('sms_gateway', 'sms_gateway.id', '=', 'error_code.gateway_id')
            ->whereNull('error_code.deleted_at')
            ->where('error_code.isActive', 1)

            ->when($gateway_ID && $gateway_ID !== 'All', function ($query) use ($gateway_ID) {
                $query->where('error_code.gateway_id', $gateway_ID);
            })

            ->when($searchText, function ($query) use ($searchText) {
                $query->where(function ($q) use ($searchText) {
                    $lowerSearch = strtolower($searchText);

                    if (in_array($lowerSearch, ['active', 'inactive'])) {
                        $status = $lowerSearch === 'active' ? 1 : 0;
                        $q->where('error_code.isActive', $status);
                    } else {
                        $q->whereRaw('LOWER(sms_gateway.gateway_name) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(error_code.error_code) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('LOWER(error_code.error_status) LIKE ?', ['%' . $lowerSearch . '%'])
                        ->orWhereRaw('error_code.created_at LIKE ?', ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(error_code.created_at, '%d-%m-%Y') LIKE ?", ['%' . $searchText . '%'])
                        ->orWhereRaw("DATE_FORMAT(error_code.created_at, '%d/%m/%Y') LIKE ?", ['%' . $searchText . '%']);
                    }
                });
            })
            ->select(
                'error_code.id',
                'sms_gateway.gateway_name as gateway',
                'error_code.error_code',
                'error_code.error_status as error_description',
                'error_code.created_at'
            );

        // Get total count
        $totalCount = (clone $query)->count();

        // Apply sorting and pagination
        $allowedSortColumns = ['gateway', 'error_code', 'error_description', 'created_at'];
        
        if (in_array($sorting, $allowedSortColumns)) {
            $query->orderBy($sorting, $orderBy);
        } else {
            $query->orderBy('error_code.created_at', 'desc');
        }

        $error_reports = $query
            ->skip($offset)
            ->take($limit)
            ->get();

        return [$error_reports, $totalCount];
    }

    public static function generateErrorDescriptionReportsDownload($gateway_ID = null)
    {
        $query = DB::table('error_code')
            ->leftJoin('sms_gateway', 'sms_gateway.id', '=', 'error_code.gateway_id')
            ->whereNull('error_code.deleted_at')
            ->where('error_code.isActive', 1);

        // Apply gateway filter
        if ($gateway_ID && $gateway_ID !== 'All') {
            $query->where('error_code.gateway_id', $gateway_ID);
        }

        $records = $query->select(
                'sms_gateway.gateway_name as gateway',
                'error_code.error_code',
                'error_code.error_status as error_description'
            )
            ->orderBy('sms_gateway.gateway_name', 'asc')
            ->orderBy('error_code.error_code', 'asc')
            ->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No records found'], 404);
        }

        $chunkSize = 400000; // 4 lakh rows per file
        $chunks = $records->chunk($chunkSize);

        $filePaths = [];
        foreach ($chunks as $index => $chunk) {
            $fileName = "Error_Description_Reports_Part_" . ($index + 1) . ".xlsx";
            $filePath = storage_path("app/public/{$fileName}");

            Excel::store(new \App\Exports\ErrorDescriptionReportsExport($chunk), "public/{$fileName}");
            $filePaths[] = $filePath;
        }

        // If only one file → return directly
        if (count($filePaths) === 1) {
            return response()->download($filePaths[0])->deleteFileAfterSend(true);
        }

        // If multiple files → create ZIP
        $zipFileName = 'Error_Description_Reports_' . now()->format('YmdHis') . '.zip';
        $zipPath = storage_path("app/public/{$zipFileName}");

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE) === TRUE) {
            foreach ($filePaths as $file) {
                $zip->addFile($file, basename($file));
            }
            $zip->close();
        }

        // Clean up individual files
        foreach ($filePaths as $file) {
            if (file_exists($file)) {
                unlink($file);
            }
        }

        return response()->download($zipPath)->deleteFileAfterSend(true);
    }

    public static function updateErrorDescription($id, $error_status)
    {
        $updated = DB::table('error_code')
            ->where('id', $id)
            ->whereNull('deleted_at')
            ->where('isActive', 1)
            ->update([
                'error_status' => $error_status,
                'updated_by' => Auth::id(),
                'updated_at' => now()
            ]);

        if (!$updated) {
            throw new \Exception('Error description not found or could not be updated');
        }

        return DB::table('error_code')
            ->where('id', $id)
            ->select('id', 'error_code', 'error_status', 'gateway_id')
            ->first();
    }

    public static function deleteErrorDescription($id)
    {
        $deleted = DB::table('error_code')
            ->where('id', $id)
            ->whereNull('deleted_at')
            ->update([
                'deleted_at' => now(),
                'isActive' => 0,
                'isDeleted' => 1,
                'deleted_by' => Auth::id(),
                'updated_at' => now()
            ]);

        if (!$deleted) {
            throw new \Exception('Error description not found or could not be deleted');
        }

        return ['id' => $id, 'message' => 'Successfully deleted'];
    }

}