<?php

namespace App\Http\Controllers;

use DB;
use App\Models\Nagad;
use App\Models\Orders;
use App\Models\Invoice;
use App\Models\InvoiceDetails;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use App\Library\SslCommerz\SslCommerzNotification;
use Closure;
use Illuminate\Support\Facades\URL;
use DateTime;
use Mail;
use DatePeriod;
use DateInterval;
use Exception;
use Carbon\Carbon;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\Log;

class CronController extends Controller
{
    private $diagMailFailures = 0;
    private $diagHttpFailures = 0;

    private function executeHttpGet($url)
    {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
        curl_setopt($ch, CURLOPT_TIMEOUT, 20);
        $response = curl_exec($ch);
        curl_close($ch);

        if ($response === false) {
            $this->diagHttpFailures++;
        }

        return $response === false ? '' : $response;
    }

    private function safeMailSend($view, array $data, Closure $callback, $context = '')
    {
        try {
            Mail::send($view, $data, $callback);
            return true;
        } catch (\Throwable $e) {
            $this->diagMailFailures++;
            Log::error('Mail send failed ['.$context.']: '.$e->getMessage());
            return false;
        }
    }

    private function emitDiagnosticLine($cronName)
    {
        $line = sprintf(
            'DIAGNOSTIC cron=%s mail_failures=%d http_failures=%d',
            $cronName,
            $this->diagMailFailures,
            $this->diagHttpFailures
        );
        echo '<br>'.$line.'<br>';
        Log::info($line);
    }

    private function deliveryGuardKey($channel, $recipient)
    {
        return 'delivery_guard:'.strtolower((string) $channel).':'.sha1(strtolower(trim((string) $recipient)));
    }

    private function isDeliveryBlocked($channel, $recipient)
    {
        $recipient = trim((string) $recipient);
        if ($recipient === '') {
            return false;
        }

        $key = $this->deliveryGuardKey($channel, $recipient);
        $state = Cache::get($key);
        if (!is_array($state) || empty($state['blocked_until'])) {
            return false;
        }

        try {
            $blockedUntil = Carbon::parse($state['blocked_until']);
            if ($blockedUntil->isFuture()) {
                return true;
            }
        } catch (\Throwable $e) {
            // Invalid timestamp: clear broken state and continue.
        }

        Cache::forget($key);
        return false;
    }

    private function markDeliveryFailure($channel, $recipient, $reason = '')
    {
        $recipient = trim((string) $recipient);
        if ($recipient === '') {
            return;
        }

        $key = $this->deliveryGuardKey($channel, $recipient);
        $state = Cache::get($key, [
            'fail_count' => 0,
            'last_failure_at' => null,
            'blocked_until' => null,
        ]);

        $now = Carbon::now();
        if (!empty($state['last_failure_at'])) {
            try {
                $lastFailure = Carbon::parse($state['last_failure_at']);
                // If last failure is old, reset failure streak.
                if ($lastFailure->lt($now->copy()->subDays(10))) {
                    $state['fail_count'] = 0;
                }
            } catch (\Throwable $e) {
                $state['fail_count'] = 0;
            }
        }

        $state['fail_count'] = (int) $state['fail_count'] + 1;
        $state['last_failure_at'] = $now->toDateTimeString();

        $threshold = 5; // Roughly "few days" of repeated failures.
        if ((int) $state['fail_count'] >= $threshold) {
            $state['blocked_until'] = $now->copy()->addDays(60)->toDateTimeString();
            Log::warning(
                'Delivery auto-blocked channel='.$channel.
                ' recipient='.$recipient.
                ' fail_count='.$state['fail_count'].
                (!empty($reason) ? ' reason='.$reason : '')
            );
        }

        Cache::put($key, $state, $now->copy()->addDays(120));
    }

    private function markDeliverySuccess($channel, $recipient)
    {
        $recipient = trim((string) $recipient);
        if ($recipient === '') {
            return;
        }

        Cache::forget($this->deliveryGuardKey($channel, $recipient));
    }

    private function applyPppoeDisconnect($API, $ppoeUser, $connectionId, $deviceId)
    {
        $ppoeUser = trim((string) $ppoeUser);
        if ($ppoeUser === '') {
            return false;
        }

        try {
            $API->write('/ppp/secret/print', false);
            $API->write('?name='.$ppoeUser, false);
            $API->write('=.proplist=.id,name,disabled');
            $secretRows = $API->read();

            if (empty($secretRows) || empty($secretRows[0]['.id'])) {
                Log::warning('DISCONNECT_FAIL method=pppoe connection_id='.$connectionId.' device_id='.$deviceId.' pppoe_user='.$ppoeUser.' reason=secret_not_found');
                return false;
            }

            $secretId = $secretRows[0]['.id'];
            $API->write('/ppp/secret/set', false);
            $API->write('=.id='.$secretId, false);
            $API->write('=disabled=yes');
            $API->read();

            // Drop active session immediately if still connected.
            $API->write('/ppp/active/print', false);
            $API->write('?name='.$ppoeUser, false);
            $API->write('=.proplist=.id');
            $activeRows = $API->read();
            if (!empty($activeRows)) {
                foreach ($activeRows as $activeRow) {
                    if (empty($activeRow['.id'])) {
                        continue;
                    }
                    $API->write('/ppp/active/remove', false);
                    $API->write('=.id='.$activeRow['.id']);
                    $API->read();
                }
            }

            Log::info('DISCONNECT_SUCCESS method=pppoe connection_id='.$connectionId.' device_id='.$deviceId.' pppoe_user='.$ppoeUser);
            return true;
        } catch (\Throwable $e) {
            Log::error('DISCONNECT_FAIL method=pppoe connection_id='.$connectionId.' device_id='.$deviceId.' pppoe_user='.$ppoeUser.' reason='.$e->getMessage());
            return false;
        }
    }

    private function readLogTailLines($path, $maxLines = 120)
    {
        if (!is_file($path) || !is_readable($path)) {
            return [];
        }

        $fileSize = @filesize($path);
        if ($fileSize === false || $fileSize <= 0) {
            return [];
        }

        $maxBytes = 262144; // 256KB window from file end keeps memory usage low.
        $readBytes = min($fileSize, $maxBytes);

        $fp = @fopen($path, 'rb');
        if (!$fp) {
            return [];
        }

        if ($fileSize > $readBytes) {
            fseek($fp, -1 * $readBytes, SEEK_END);
        } else {
            rewind($fp);
        }

        $buffer = stream_get_contents($fp);
        fclose($fp);

        if ($buffer === false || $buffer === '') {
            return [];
        }

        $lines = preg_split("/\r\n|\n|\r/", $buffer);
        if (!is_array($lines)) {
            return [];
        }

        // Drop first partial line when reading from middle of large files.
        if ($fileSize > $readBytes && count($lines) > 0) {
            array_shift($lines);
        }

        $lines = array_values(array_filter($lines, function ($line) {
            return trim((string) $line) !== '';
        }));

        return array_slice($lines, -1 * max(1, (int) $maxLines));
    }

    private function buildDailyCronHealthSnapshot()
    {
        $cronLogs = [
            'Recurring (regular)' => '/home/infolink/recurring_cron.log',
            'Recurring (prepaid)' => '/home/infolink/recurring_prepaid_cron.log',
            'Disconnect all devices' => '/home/infolink/device_disconnect.log',
            'Due SMS' => '/home/infolink/due_sms.log',
            'Due SMS (prepaid)' => '/home/infolink/due_sms_prepaid.log',
            'Due SMS (bangla)' => '/home/infolink/due_sms_bangla.log',
            'Due Email' => '/home/infolink/due_email.log',
            'Payment clear' => '/home/infolink/payment_clear.log',
            'SSL payment check' => '/home/infolink/ssl_payment_check.log',
            'Daily report' => '/home/infolink/daily_report.log',
        ];

        $summary = [];
        foreach ($cronLogs as $name => $path) {
            $tail = $this->readLogTailLines($path, 80);
            $latestLine = '';
            $latestSuccessIndex = -1;
            $latestIssueIndex = -1;

            foreach ($tail as $idx => $lineRaw) {
                $line = trim(strip_tags((string) $lineRaw));
                if ($line === '') {
                    continue;
                }

                $lineLower = strtolower($line);
                if (
                    strpos($lineLower, 'starting ') !== false ||
                    strpos($lineLower, 'x-powered-by:') === 0 ||
                    strpos($lineLower, 'content-type:') === 0
                ) {
                    continue;
                }

                $latestLine = $line;

                if (
                    strpos($lineLower, 'completed') !== false ||
                    strpos($lineLower, 'diagnostic') !== false ||
                    strpos($lineLower, 'cron runned successfully') !== false
                ) {
                    $latestSuccessIndex = $idx;
                }

                if (
                    strpos($lineLower, 'failed') !== false ||
                    strpos($lineLower, 'fatal') !== false ||
                    strpos($lineLower, 'error') !== false
                ) {
                    $latestIssueIndex = $idx;
                }
            }

            if (strlen($latestLine) > 240) {
                $latestLine = substr($latestLine, 0, 240).'...';
            }

            $status = 'NO_LOG';
            if (!empty($tail)) {
                if ($latestSuccessIndex >= 0 && $latestSuccessIndex >= $latestIssueIndex) {
                    $status = 'OK';
                } elseif ($latestIssueIndex >= 0) {
                    $status = 'ISSUE';
                } else {
                    $status = 'UNKNOWN';
                }
            }

            $summary[] = [
                'name' => $name,
                'status' => $status,
                'latest_line' => $latestLine,
                'path' => $path,
            ];
        }

        $recentErrors = [];
        $recentSuccesses = [];
        $lookbackHours = 24;
        $cutoff = Carbon::now()->subHours($lookbackHours);
        $disconnectStats = [
            'total_devices' => 0,
            'success_devices' => 0,
            'failed_devices' => 0,
            'disconnect_ip_success' => 0,
            'disconnect_pppoe_success' => 0,
            'disconnect_pppoe_failures' => 0,
            'disconnect_apply_failures' => 0,
            'disconnect_not_applied' => 0,
            'device_connect_failures' => 0,
            'failure_reasons' => [],
        ];
        $reconnectStats = [
            'success' => 0,
            'ip_success' => 0,
            'pppoe_success' => 0,
            'failed' => 0,
            'failure_reasons' => [],
        ];
        $laravelTail = $this->readLogTailLines(storage_path('logs/laravel.log'), 500);
        foreach ($laravelTail as $line) {
            if (preg_match('/^\[([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})\]/', $line, $m)) {
                try {
                    $logTime = Carbon::createFromFormat('Y-m-d H:i:s', $m[1]);
                    if ($logTime->lt($cutoff)) {
                        continue;
                    }
                } catch (\Throwable $e) {
                    continue;
                }
            } else {
                continue;
            }

            $lineLower = strtolower($line);
            if (preg_match('/DIAGNOSTIC cron=device_wise_disconnect_all total_devices=([0-9]+) success=([0-9]+) failed=([0-9]+)/', $line, $parts)) {
                $disconnectStats['total_devices'] += (int) $parts[1];
                $disconnectStats['success_devices'] += (int) $parts[2];
                $disconnectStats['failed_devices'] += (int) $parts[3];
            }
            if (strpos($line, 'Disconnect apply failed for CID-') !== false) {
                $disconnectStats['disconnect_apply_failures']++;
                $reason = 'unknown';
                $chunks = explode(': ', $line, 2);
                if (isset($chunks[1]) && trim($chunks[1]) !== '') {
                    $reason = trim($chunks[1]);
                }
                if (!isset($disconnectStats['failure_reasons'][$reason])) {
                    $disconnectStats['failure_reasons'][$reason] = 0;
                }
                $disconnectStats['failure_reasons'][$reason]++;
            }
            if (strpos($line, 'Disconnect not applied for CID-') !== false) {
                $disconnectStats['disconnect_not_applied']++;
                if (!isset($disconnectStats['failure_reasons']['disconnect_not_applied'])) {
                    $disconnectStats['failure_reasons']['disconnect_not_applied'] = 0;
                }
                $disconnectStats['failure_reasons']['disconnect_not_applied']++;
            }
            if (strpos($line, 'DISCONNECT_SUCCESS method=ip ') !== false) {
                $disconnectStats['disconnect_ip_success']++;
            }
            if (strpos($line, 'DISCONNECT_SUCCESS method=pppoe ') !== false) {
                $disconnectStats['disconnect_pppoe_success']++;
            }
            if (strpos($line, 'DISCONNECT_FAIL method=pppoe ') !== false) {
                $disconnectStats['disconnect_pppoe_failures']++;
                $reason = 'unknown';
                if (preg_match('/reason=([^ ]+)/', $line, $reasonMatch)) {
                    $reason = trim($reasonMatch[1]);
                }
                if (!isset($disconnectStats['failure_reasons'][$reason])) {
                    $disconnectStats['failure_reasons'][$reason] = 0;
                }
                $disconnectStats['failure_reasons'][$reason]++;
            }
            if (strpos($line, 'DISCONNECT_FAIL method=ip ') !== false) {
                $disconnectStats['disconnect_apply_failures']++;
                $reason = 'unknown';
                if (preg_match('/reason=([^ ]+)/', $line, $reasonMatch)) {
                    $reason = trim($reasonMatch[1]);
                }
                if (!isset($disconnectStats['failure_reasons'][$reason])) {
                    $disconnectStats['failure_reasons'][$reason] = 0;
                }
                $disconnectStats['failure_reasons'][$reason]++;
            }
            if (strpos($line, 'Device connection failed for device-') !== false) {
                $disconnectStats['device_connect_failures']++;
                if (!isset($disconnectStats['failure_reasons']['device_connect_failed'])) {
                    $disconnectStats['failure_reasons']['device_connect_failed'] = 0;
                }
                $disconnectStats['failure_reasons']['device_connect_failed']++;
            }
            if (strpos($line, 'RECONNECT_SUCCESS') !== false) {
                $reconnectStats['success']++;
            }
            if (strpos($line, 'RECONNECT_SUCCESS method=ip ') !== false) {
                $reconnectStats['ip_success']++;
            }
            if (strpos($line, 'RECONNECT_SUCCESS method=pppoe ') !== false) {
                $reconnectStats['pppoe_success']++;
            }
            if (strpos($line, 'RECONNECT_FAIL') !== false) {
                $reconnectStats['failed']++;
                $reason = 'unknown';
                if (preg_match('/reason=([^ ]+)/', $line, $reasonMatch)) {
                    $reason = trim($reasonMatch[1]);
                }
                if (!isset($reconnectStats['failure_reasons'][$reason])) {
                    $reconnectStats['failure_reasons'][$reason] = 0;
                }
                $reconnectStats['failure_reasons'][$reason]++;
            }

            $isCronRelated = (
                strpos($lineLower, 'cron') !== false ||
                strpos($lineLower, 'mail send failed') !== false ||
                strpos($lineLower, 'auto_create_invoice failed') !== false ||
                strpos($lineLower, 'device_wise_disconnect') !== false ||
                strpos($lineLower, 'due_payments_') !== false ||
                strpos($lineLower, 'reconnect_') !== false
            );
            if (!$isCronRelated) {
                continue;
            }

            if (strpos($line, ' local.ERROR: ') !== false) {
                $recentErrors[] = $line;
                continue;
            }

            if (
                strpos($line, ' local.INFO: ') !== false &&
                (
                    strpos($lineLower, 'completed') !== false ||
                    strpos($lineLower, 'diagnostic cron=') !== false
                )
            ) {
                $recentSuccesses[] = $line;
            }
        }

        return [
            'summary' => $summary,
            'recent_errors' => array_slice($recentErrors, -20),
            'recent_successes' => array_slice($recentSuccesses, -20),
            'disconnect_stats' => $disconnectStats,
            'reconnect_stats' => $reconnectStats,
            'lookback_hours' => $lookbackHours,
        ];
    }

    public function due_payments_sms(){
	    
	    $sms = DB::table('sms_template')
                ->join('sms_settings', 'sms_template.sms_settings_id', '=', 'sms_settings.id')
                ->select('sms_settings.link','sms_template.*')
                ->where('sms_template.id',7)
                ->first();
        if (!$sms || empty($sms->link) || empty($sms->message)) {
            Log::error('due_payments_sms skipped: sms template/settings missing');
            return;
        }
                
         date_default_timezone_set('Asia/Dhaka');
        //  echo date('Y-m-d');
        //  exit();
	    $due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (1) and invoice.client_id=client.client_id and client.last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' and client.client_phone1 != '' and client.client_phone1 LIKE '880%' GROUP by invoice.client_id");
	    //echo count($due_sms);die;
	    /*$update_sms = DB::select("UPDATE client
                    SET client.last_bill_summary_sms_date = CURDATE()
                    WHERE client.client_id IN (
                        SELECT DISTINCT invoice.client_id
                        FROM invoice
                        WHERE invoice.status = 1
                    ) AND client.last_bill_summary_sms_date NOT LIKE CONCAT(CURDATE(), '%');");
        
        echo 1;die;*/
        $i = 0;
        if($due_sms){
	            foreach($due_sms as $row){
                    try {
                        if ($this->isDeliveryBlocked('sms', $row->client_phone1)) {
                            Log::info('SMS skipped due to temporary block for '.$row->client_phone1.' client_id '.$row->client_id);
                            continue;
                        }

    	                $n1 = str_replace('[[client_title]]',$row->client_title,$sms->message);
    	                $n2 = str_replace('[[client_id]]',$row->client_id,$n1);
    	                $encoded_message = urlencode($n2);
    	                $m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
    	                $api = str_replace('[[gsm]]',"$row->client_phone1",$m1);
    	                
    	                $message = $this->executeHttpGet($api);
        				
        				if(strpos($message, "200") !== false){
        				    $data_update = array(
                				'last_bill_summary_sms_date' => date('Y-m-d')
                			);
                    		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
                            $this->markDeliverySuccess('sms', $row->client_phone1);
                    		
                            echo $row->client_id." - SMS Send Done.<br>";
                    		$i++;
                		
                        } else {
                            $this->markDeliveryFailure('sms', $row->client_phone1, 'provider_response_non_200');
                        }
                    } catch (\Throwable $e) {
                        $this->markDeliveryFailure('sms', $row->client_phone1, $e->getMessage());
                        Log::error('due_payments_sms failed for client_id '.$row->client_id.': '.$e->getMessage());
                    }
	                
	            }
        }
        
        echo "Total -".$i." SMS Send";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',13)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
        $this->emitDiagnosticLine('due_payments_sms');
		
	    
	}
	
	public function due_payments_sms_arafat(){
	    
	    $sms = DB::table('sms_template')
                ->join('sms_settings', 'sms_template.sms_settings_id', '=', 'sms_settings.id')
                ->select('sms_settings.link','sms_template.*')
                ->where('sms_template.id',7)
                ->first();
        
         date_default_timezone_set('Asia/Dhaka');
        
	    $due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (1) and invoice.client_id=client.client_id and client.last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' and client.client_phone1 != '' and client.client_phone1 LIKE '880%' GROUP by invoice.client_id");
	    
        $i = 0;
        if($due_sms){
	            foreach($due_sms as $row){
	                
	                if($row->client_id == 7){
	                
	                $n1 = str_replace('[[client_title]]',$row->client_title,$sms->message);
	                $n2 = str_replace('[[client_id]]',$row->client_id,$n1);
	                $encoded_message = urlencode($n2);
	                $m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
	                $api = str_replace('[[gsm]]',"$row->client_phone1",$m1);
	                
	                echo $api;
	                die;
	                
	                $message = $this->executeHttpGet($api);
    				
    				//old
    				//echo $message;
                    //if(strpos($message, "SMS SUBMITTED") !== false){
                    
                    
                    
                    
    				// if(strpos($message, "200") !== false){
    				//     $data_update = array(
        //     				'last_bill_summary_sms_date' => date('Y-m-d')
        //     			);
        //         		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
                		
        //                 echo $row->client_id." - SMS Send Done.<br>";
        //         		$i++;
            		
        //             }
    				//exit();
    				//echo $row->client_id." - SMS Send Done.<br>";
    				
    				
	                }
	            }
        }
        
        die;
        
        echo "Total -".$i." SMS Send";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',13)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
		
	    
	}
	
	public function due_payments_email_Old(){
	    
	    $sms = DB::table('sms_template')
                ->join('sms_settings', 'sms_template.sms_settings_id', '=', 'sms_settings.id')
                ->select('sms_settings.link','sms_template.*')
                ->where('sms_template.id',7)
                ->first();
                                        
	    $due_email = DB::select("SELECT invoice.invoice_id,invoice.client_id,client.client_title,client.client_phone1,client.client_email1 FROM invoice,client WHERE invoice.status IN (1) and invoice.client_id=client.client_id and client.client_email1_status != '0' and client.last_bill_summary_date NOT LIKE '".date('Y-m-d')."%' GROUP by invoice.client_id");
	    //$due_email = DB::select("SELECT client_id,client_email1 FROM client WHERE client_id in (SELECT client_id FROM invoice_recurring WHERE status = 1) and client_email1 not like '880%' and last_bill_summary_date NOT LIKE '".date('Y-m-d')."%' ");
	    //$due_email = DB::select("SELECT client_id,client_email1 FROM client WHERE client_id in (4705,8238) and client_email1 not like '880%'; ");
        $i = 0;
        if($due_email){
	            foreach($due_email as $row){
	               //if($row->client_id == '4705'){
	                
	                $notification 	= DB::table('notification')->where('id',4)->first();
            
                    $type = $notification->type;
        	        $subject = $notification->subject;
        	        //$subject = "সুরক্ষা মোডে সাময়িক সমস্যা – আপনার সহযোগিতার জন্য ধন্যবাদ";
        	        $from_email = $notification->from_email;
        	        $from_name = $notification->from_name;
        	        
        	        
        	        $client_info = DB::table('client')->where('client_id',$row->client_id)->first();
            			
        			$c_email = array();
        			if($client_info->client_email1_status != 0){
        			    $emails = [$client_info->client_email1];
        			    $c_email = array($client_info->client_email1);
        			}elseif($client_info->client_email2_status != 0){
        			    $emails = [$client_info->client_email2];
        			    $c_email = array($client_info->client_email2);
        			}elseif($client_info->client_email1_status != 0 AND $client_info->client_email2_status != 0){
        			    $emails = [$client_info->client_email1,$client_info->client_email2];
        			    $c_email = array($client_info->client_email1,$client_info->client_email2);
        			}
        			
        			if(!empty($c_email)){
            		    //$data = array('invoice_id'=>$row->invoice_id,'client_id'=>$row->client_id);
            		    $data = array('client_id'=>$row->client_id);
                        $mailSent = $this->safeMailSend('mail_template.due_reminder', $data, function($message) use ($emails,$type,$subject,$from_email,$from_name) {
                        //Mail::send('mail_template.employee_announcement_due', $data, function($message) use ($emails,$type,$subject,$from_email,$from_name) {
                            //  echo $message;announcement_due
                            //  die();
                             $message->to($emails, $type)->subject($subject);
                             $message->from($from_email,$from_name);
                        }, 'due_payments_email_old');
                        
                        if ($mailSent) {
                            $data_update = array(
                				'last_bill_summary_date' => date('Y-m-d H:i:s')
                			);
                    		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
                        }
        			}
        			
        			echo $row->client_id." - Email Send Done.<br>";
                        
                		$i++;
	        
	        
    				
    				
			
	            //}
			
			
			
    				
    				
	                
	            }
        }
        
        echo "Total -".$i." Email Send";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',12)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
		
	    
	}
	
	//updated by arafat..
	public function due_payments_email() {
        $notification = DB::table('notification')->where('id',4)->first();
        if (!$notification) {
            Log::error('due_payments_email skipped: notification template missing');
            return;
        }
    
        $type        = $notification->type;
        $subject     = $notification->subject;
        $from_email  = $notification->from_email;
        $from_name   = $notification->from_name;
    
        $due_email = DB::select("
            SELECT i.invoice_id,i.client_id,c.client_title,c.client_phone1,
                   c.client_email1,c.client_email2,
                   c.client_email1_status,c.client_email2_status
            FROM invoice AS i
            JOIN client AS c ON i.client_id = c.client_id
            WHERE i.status = 1 
              AND c.client_email1_status != '0' 
              AND c.last_bill_summary_date NOT LIKE '".date('Y-m-d')."%'
            GROUP BY i.client_id
        ");
    
        $i = 0;
        $invalidEmails = []; // for invalid email list collect
    
        if ($due_email) {
            foreach ($due_email as $row) {
                try {
    
                $emails = [];
    
                if (!empty($row->client_email1) && $row->client_email1_status != 0) {
                    $emails[] = $row->client_email1;
                }
                if (!empty($row->client_email2) && $row->client_email2_status != 0) {
                    $emails[] = $row->client_email2;
                }
    
                // validate emails only
                $validEmails = array_filter($emails, function($email) {
                    return filter_var($email, FILTER_VALIDATE_EMAIL);
                });
    
                // invalid email collecting
                $invalidEmails = array_merge(
                    $invalidEmails,
                    array_diff($emails, $validEmails)
                );
    
                if (!empty($validEmails)) {
                    $allowedEmails = [];
                    foreach ($validEmails as $email) {
                        if ($this->isDeliveryBlocked('email', $email)) {
                            Log::info('Email skipped due to temporary block for '.$email.' client_id '.$row->client_id);
                            continue;
                        }
                        $allowedEmails[] = $email;
                    }

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

                    $data = [
                        'client_id'    => $row->client_id,
                        'client_title' => $row->client_title
                    ];
    
                    $mailSent = $this->safeMailSend('mail_template.due_reminder_new', $data, function($message) use ($allowedEmails, $subject, $from_email, $from_name) {
                        $message->to($allowedEmails)->subject($subject);
                        $message->from($from_email, $from_name);
                    }, 'due_payments_email');

                    if ($mailSent) {
                        foreach ($allowedEmails as $email) {
                            $this->markDeliverySuccess('email', $email);
                        }
                        DB::table('client')
                            ->where('client_id', $row->client_id)
                            ->update(['last_bill_summary_date' => date('Y-m-d')]);
    
                        echo $row->client_id . " - Email Send Done.<br>";
                        $i++;
                    } else {
                        foreach ($allowedEmails as $email) {
                            $this->markDeliveryFailure('email', $email, 'mail_send_failed');
                        }
                    }
                }
                } catch (\Throwable $e) {
                    foreach ($validEmails ?? [] as $email) {
                        $this->markDeliveryFailure('email', $email, $e->getMessage());
                    }
                    Log::error('due_payments_email failed for client_id '.$row->client_id.': '.$e->getMessage());
                }
            }
        }
    
        echo "Total - " . $i . " Email Send<br>";
    
        // show invalid email
        if (!empty($invalidEmails)) {
            echo "<br><b>Invalid Emails:</b><br>";
            foreach ($invalidEmails as $bad) {
                echo $bad . "<br>";
            }
        }
    
        $cron_log = [
            'run_time' => date('Y-m-d H:i:s')
        ];
        $update = DB::table('cron_settings')->where('cron_id', 12)->update($cron_log);
    
        if ($update) {
            echo 'Cron Runned Successfully';
        } else {
            echo 'Nothing to Update';
        }
        $this->emitDiagnosticLine('due_payments_email');
    }

	
	public function due_payments_sms_bangla(){
	    
	    $sms = DB::table('sms_template')
                ->join('sms_settings', 'sms_template.sms_settings_id', '=', 'sms_settings.id')
                ->select('sms_settings.link','sms_template.*')
                ->where('sms_template.id',27)
                ->first();
        if (!$sms || empty($sms->link) || empty($sms->message)) {
            Log::error('due_payments_sms_bangla skipped: sms template/settings missing');
            return;
        }
                                        
	    $due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (1) and invoice.client_id=client.client_id and client.last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' GROUP by invoice.client_id");
        //$due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (1) and invoice.client_id=client.client_id and client.client_id='7' GROUP by invoice.client_id");
        //$due_sms = DB::select("SELECT DISTINCT(client_phone1),client_title,client_id FROM client WHERE client_id in (SELECT client_id FROM invoice_recurring WHERE status = 1) and last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' ");
        
        
        $i = 0;
        if($due_sms){
	            foreach($due_sms as $row){
                    try {
                        if ($this->isDeliveryBlocked('sms', $row->client_phone1)) {
                            Log::info('SMS Bangla skipped due to temporary block for '.$row->client_phone1.' client_id '.$row->client_id);
                            continue;
                        }

    	                $n1 = str_replace('[[client_title]]',$row->client_title,$sms->message);
    	                $n2 = str_replace('[[client_id]]',$row->client_id,$n1);
    	                $encoded_message = urlencode($n2);
    	                $m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
    	                $api = str_replace('[[gsm]]',"$row->client_phone1",$m1);
    	                
    	                $message = $this->executeHttpGet($api);
        				
        				if(strpos($message, "200") !== false){
                            echo $row->client_id." - SMS Send Done.<br>";
                            $data_update = array(
                				'last_bill_summary_sms_date' => date('Y-m-d H:i:s')
                			);
                    		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
                            $this->markDeliverySuccess('sms', $row->client_phone1);
                    		$i++;
                		
                        } else {
                            $this->markDeliveryFailure('sms', $row->client_phone1, 'provider_response_non_200');
                        }
                    } catch (\Throwable $e) {
                        $this->markDeliveryFailure('sms', $row->client_phone1, $e->getMessage());
                        Log::error('due_payments_sms_bangla failed for client_id '.$row->client_id.': '.$e->getMessage());
                    }
	                
	            }
        }
        
        echo "Total -".$i." SMS Send";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',13)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
        $this->emitDiagnosticLine('due_payments_sms_bangla');
		
	    
	}
	
	public function due_payments_sms_prepaid(){
	    
	    $sms = DB::table('sms_template')
                ->join('sms_settings', 'sms_template.sms_settings_id', '=', 'sms_settings.id')
                ->select('sms_settings.link','sms_template.*')
                ->where('sms_template.id',7)
                ->first();
        if (!$sms || empty($sms->link) || empty($sms->message)) {
            Log::error('due_payments_sms_prepaid skipped: sms template/settings missing');
            return;
        }
                                        
	    $due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (1) and invoice.frequency='p1' and invoice.client_id=client.client_id and client.last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' GROUP by invoice.client_id");
        
        // print_r($due_sms);
        // exit();
        $i = 0;
        if($due_sms){
	            foreach($due_sms as $row){
                    try {
                        if ($this->isDeliveryBlocked('sms', $row->client_phone1)) {
                            Log::info('SMS prepaid skipped due to temporary block for '.$row->client_phone1.' client_id '.$row->client_id);
                            continue;
                        }

    	                $n1 = str_replace('[[client_title]]',$row->client_title,$sms->message);
    	                $n2 = str_replace('[[client_id]]',$row->client_id,$n1);
    	                $encoded_message = urlencode($n2);
    	                $m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
    	                $api = str_replace('[[gsm]]',"$row->client_phone1",$m1);
    	                
    	                $message = $this->executeHttpGet($api);
        				
        				if(strpos($message, "200") !== false){
            				echo $row->client_id." - SMS Send Done.<br>";
            				
            				$data_update = array(
                    				'last_bill_summary_sms_date' => date('Y-m-d H:i:s')
                    			);
                    		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
                            $this->markDeliverySuccess('sms', $row->client_phone1);
                    		$i++;
    					}
                        else {
                            $this->markDeliveryFailure('sms', $row->client_phone1, 'provider_response_non_200');
                        }
                    } catch (\Throwable $e) {
                        $this->markDeliveryFailure('sms', $row->client_phone1, $e->getMessage());
                        Log::error('due_payments_sms_prepaid failed for client_id '.$row->client_id.': '.$e->getMessage());
                    }
	                
	            }
        }
        
        echo "Total -".$i." SMS Send";
        $this->emitDiagnosticLine('due_payments_sms_prepaid');
	    
	}
	
	public function ping_monitor_ip($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
	    
	    $newTime = strtotime('-30 minutes');
	    $cur_time = date('Y-m-d H:i:s', $newTime);
	    
	    $device_id = $id;
	    //$connection_list = DB::select("SELECT * FROM connection_list WHERE link_status=1 and device_id = $device_id ");
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE connection_id in (SELECT connection_id FROM connection_list WHERE link_status = 1 AND device_id = $device_id) and status=1 and last_ping < '$cur_time' order by last_ping ASC limit 200");
	    
	    $device = DB::table('device_settings')->where('id',$device_id)->first();
		include_once(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = false;
        
        $API->connect($device->device_ip, $device->username, $device->password);
                            
	    //$monitor_ip_list = DB::select("SELECT monitor_id,ip FROM connection_monitor_ip ");
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                
	                //$connection_ip = DB::select("SELECT * FROM connection_ip where connection_id = $row->connection_id and type = 'ipv_four' ");
	                
	               // if($connection_ip){
	               //     foreach($connection_ip as $ips){
	                        
	                        $API->write('/ping',false);
                            $API->write('=address='.$row->ip.'',false); 
                            $API->write('=count=3',false);
                            $API->write('=interval=1');
                            $ARRAY4 = $API->read(false);
                            //echo "<pre>". print_r($ARRAY4) ." </pre>";
                            
                            
                            
                            if(stripos(json_encode($ARRAY4),'=packet-loss=100') !== false){
                                 echo "CID-".$row->connection_id." - ".$row->ip." - ";
                                echo " - line down <br>";
                                
                                $data_update = array(
                        				'ping' => 2,
                        				'last_ping' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }else{
                                $data_update = array(
                        				'ping' => 1,
                        				'last_ping' => date('Y-m-d H:i:s'),
                        				'last_up' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }
                    
                            
                            $i++;
	                        
	               //     }
	               // }
	                
	            }
        }
        
        $API->disconnect();
        
        echo "Total -".$i." Ping Done";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',17)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
	}
	
	public function ping_monitor_ip_bandwidth($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
	    
	    $newTime = strtotime('-30 minutes');
	    $cur_time = date('Y-m-d H:i:s', $newTime);
	    
	    $device_id = $id;
	    //$connection_list = DB::select("SELECT * FROM connection_list WHERE link_status=1 and device_id = $device_id ");
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE connection_id in (SELECT connection_id FROM connection_list WHERE link_status = 1 AND device_id = $device_id) and status=1 and client_id='7955' order by last_ping ASC limit 200");
	    //and last_ping < '$cur_time' 
	   // print_r($connection_list);
	   // exit();
	    $device = DB::table('device_settings')->where('id',$device_id)->first();
		include_once(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = false;
        
        $API->connect($device->device_ip, $device->username, $device->password);
                            
	    //$monitor_ip_list = DB::select("SELECT monitor_id,ip FROM connection_monitor_ip ");
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                
	                //$connection_ip = DB::select("SELECT * FROM connection_ip where connection_id = $row->connection_id and type = 'ipv_four' ");
	                
	               // if($connection_ip){
	               //     foreach($connection_ip as $ips){
	                        
	                        
	                       // $API->write('/tool/bandwidth-test',false);
                        //     $API->write('=address=172.23.104.19',false);
                        //     $API->write('=duration=10',false);
                        //     $API->write('=protocal=udp',false);
                        //     $API->write('=direction=both');

	                        //$API->write('/tool bandwidth-test address=172.23.104.19 interval=1 duration=10', null, 'm');
                            // $API->write('=address='.$row->ip.'',false); 
                            // $API->write('=count=3',false);
                            // $API->write('=interval=1');
                            //$ARRAY4 = $API->read(false);
                            
                            $arrRXTX=$API->comm("/interface/monitor-traffic", array( ".proplist"=> "rx-bits-per-second,tx-bits-per-second", "?interface" => "ether2",));
                            print_r($arrRXTX);
	
                            //echo "<pre>". print_r($ARRAY4) ." </pre>";
                            
                            exit();
                            
                            
                            
                            if(stripos(json_encode($ARRAY4),'=packet-loss=100') !== false){
                                 echo "CID-".$row->connection_id." - ".$row->ip." - ";
                                echo " - line down <br>";
                                
                                $data_update = array(
                        				'ping' => 2,
                        				'last_ping' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }else{
                                $data_update = array(
                        				'ping' => 1,
                        				'last_ping' => date('Y-m-d H:i:s'),
                        				'last_up' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }
                    
                            
                            $i++;
	                        
	               //     }
	               // }
	                
	            }
        }
        
        $API->disconnect();
        
        echo "Total -".$i." Ping Done";
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',17)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
	}
	
	public function ping_monitor_no_activity(){
	    
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE last_ping = '0000-00-00 00:00:00'");
	                        
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                        
	                        $this->ping_monitor_by_cid($row->connection_id);
                    
                            $i++;
	            }
        }
        echo "Total -".$i." Ping Done";
        
	}
	
	public function ping_monitor_status_off(){
	    
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE status !=2 and last_up + interval 7 day <= now() ORDER BY last_ping DESC ");
	    
	    
	    
	    $connection_list = $connection_list + DB::select("SELECT * FROM connection_monitor_ip WHERE status !=2 and connection_id not in (SELECT connection_id FROM connection_list WHERE link_status = 1)");
	    
	    $connection_list = $connection_list + DB::select("SELECT * FROM connection_monitor_ip WHERE connection_id not in (SELECT connection_id FROM connection_list WHERE link_status = 1 AND device_id in (1,2)) and status!=2");
	                        
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                
	                echo 'MID-'.$row->monitor_id.'<br>';
	                
	                        
	                        $data_update = array(
                        				'status' => 2,
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
	            }
        }
	}
	
	public function ping_monitor_by_cid($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
	    
	    $newTime = strtotime('-5 minutes');
	    $cur_time = date('Y-m-d H:i:s', $newTime);
	    
	    $connection_info = DB::table('connection_list')->where('connection_id',$id)->first();
	    
	    $device_id = $connection_info->device_id;
	    $connection_list = DB::select("SELECT * FROM connection_monitor_ip WHERE status=1 and connection_id = $id");
	    
	    $device = DB::table('device_settings')->where('id',$device_id)->first();
		include_once(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = false;
        
        $API->connect($device->device_ip, $device->username, $device->password);
                            
        $i = 0;
        if($connection_list){
	            foreach($connection_list as $row){
	                        
	                        $API->write('/ping',false);
                            $API->write('=address='.$row->ip.'',false); 
                            $API->write('=count=5',false);
                            $API->write('=interval=1');
                            
                            // /tool/ping address=10.155.126.252 count=5 interval=200ms
                            
                            ///$API->write('/ping'.$row->ip.' arp-ping=yes interface=Summit_Kasba_2358_Shared',false);
                            
                            // $API->write('/ip arp print',false);
                            // $API->write('=address='.$row->ip.'',false); 
                            // $API->write('=count=3',false);
                            // $API->write('=interval=1');
                            
                            $ARRAY4 = $API->read(false);
                            echo "<pre>". print_r($ARRAY4) ." </pre>";
                            
                            if(stripos(json_encode($ARRAY4),'=packet-loss=100') !== false){
                                 echo "CID-".$row->connection_id." - ".$row->ip." - ";
                                echo " - line down <br>";
                                
                                $data_update = array(
                        				'ping' => 2,
                        				'last_ping' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }else{
                                $data_update = array(
                        				'ping' => 1,
                        				'last_ping' => date('Y-m-d H:i:s'),
                        				'last_up' => date('Y-m-d H:i:s')
                        			);
                        		DB::table('connection_monitor_ip')->where('monitor_id',$row->monitor_id)->update($data_update);
                            }
                    
                            $i++;
	            }
        }
        
        $API->disconnect();
        
        echo "Total -".$i." Ping Done";
        
	}
	
	public function send_notify(Request $request)
	{
		$invoice_id = $request->invoice_id;
		$client_id = $request->client_id;
		$email_notification = $request->email_notification;
		$sms_notification = $request->sms_notification;
		if($email_notification == 1){
		    //sent email
		    $notification 	= DB::table('notification')->where('id',4)->first();
            
            $type = $notification->type;
	        $subject = $notification->subject;
	        $from_email = $notification->from_email;
	        $from_name = $notification->from_name;
			
			$client_info = DB::table('client')->where('client_id',$client_id)->first();
    			
			$c_email = array();
			if($client_info->client_email1_status != 0){
			    $emails = [$client_info->client_email1];
			    $c_email = array($client_info->client_email1);
			}elseif($client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email2];
			    $c_email = array($client_info->client_email2);
			}elseif($client_info->client_email1_status != 0 AND $client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email1,$client_info->client_email2];
			    $c_email = array($client_info->client_email1,$client_info->client_email2);
			}
			
			if(!empty($c_email)){
    		    $data = array('invoice_id'=>$invoice_id,'client_id'=>$client_id);
                $mailSent = $this->safeMailSend('mail_template.due_reminder', $data, function($message) use ($emails,$type,$subject,$from_email,$from_name) {
                    //  echo $message;
                    //  die();
                     $message->to($emails, $type)->subject($subject);
                     $message->from($from_email,$from_name);
                }, 'send_notify');
                if (!$mailSent) {
                    $request->session()->flash('status_notify','Email Send Failed');
                    return;
                }
			}
            $request->session()->flash('status_notify','Email Send Successfully');
		}
	}
	
	public function bad_debt_sms(){
	    
	    $sms = DB::table('sms_template')
                ->join('sms_settings', 'sms_template.sms_settings_id', '=', 'sms_settings.id')
                ->select('sms_settings.link','sms_template.*')
                ->where('sms_template.id',7)
                ->first();
                                        
	    $due_sms = DB::select("SELECT invoice.client_id,client.client_title,client.client_phone1 FROM invoice,client WHERE invoice.status IN (2,6) and invoice.client_id=client.client_id and client.last_bill_summary_sms_date NOT LIKE '".date('Y-m-d')."%' GROUP by invoice.client_id");
        $i = 0;
        if($due_sms){
	            foreach($due_sms as $row){
	                
	                $n1 = str_replace('[[client_title]]',$row->client_title,$sms->message);
	                $n2 = str_replace('[[client_id]]',$row->client_id,$n1);
	                $encoded_message = urlencode($n2);
	                $m1 = str_replace('[[sms]]',"$encoded_message",$sms->link);
	                $api = str_replace('[[gsm]]',"$row->client_phone1",$m1);
	                
	                $message = $this->executeHttpGet($api);
    				echo $row->client_id." - SMS Send Done.<br>";
    				
    				$data_update = array(
            				'last_bill_summary_sms_date' => date('Y-m-d H:i:s')
            			);
            		DB::table('client')->where('client_id',$row->client_id)->update($data_update);
            		$i++;
	                
	            }
        }
        
        echo "Total -".$i." SMS Send";
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',6)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
		
	    
	}
	
	public function device_automation_cron_by_connection($id = NULL){
	    
		$connection_id = $id;
		$ip_result = DB::select("SELECT connection_list.auto_disconnect,connection_list.client_id, connection_list.auto_disconnect,connection_list.device_id,connection_ip.* 
		                        FROM connection_list INNER JOIN connection_ip ON connection_list.connection_id = connection_ip.connection_id 
		                        WHERE connection_ip.connection_id = $connection_id");
        //$i=0;
        
        include_once(app_path() . '/NatApi/routeros_api.class.php');
				    
        $API = new \RouterosAPI();
            
        $API->debug = false;
            		
		//Remove NAT
		if(!empty($ip_result)){
			foreach($ip_result as $value){
				
				$device = DB::table('device_settings')->where('id',$value->device_id)->first();
				if($device){
				    if ($API->connect($device->device_ip, $device->username, $device->password)) {
				        
				        $response = $API->comm("/ip/firewall/address-list/add\n=list=payment_reminder\n=address=".$value->ip_address."\n=comment=ILC-".$value->client_id."__CID-".$connection_id);
		                echo '<pre>';
		                print_r($response);die;
				        if($value->type == 'ipv_six'){
				            $API->comm("/ipv6/firewall/address-list/add\n=list=payment_reminder\n=address=".$value->ip_address."\n=comment=ILC-".$value->client_id."__CID-".$connection_id);
				        }
				        $data = array(
                			'admin_id' 	=> Session::get('admin_id'), 
                			'client_id' 	=> $value->client_id, 
                			'connection_id' 	=> $connection_id, 
                			'nat_add_time' 	=> date("Y-m-d H:i:s"), 
                			'note' => "Manual", 
                			'status' => 1, 
                		);
                		$insert= DB::table('billing_automation')->insert($data);
                		
				    }
				}
			}
			/*------- Start System Log  insert -------*/
                
            echo 'found';

                /*------- End System Log  insert -------*/
                
			//Session::flash('msg','Disconnected Successfully CID-'.$connection_id);
		    //return redirect('automation');
			//return back()->withErrors(['Disconnected Successfully !!!']);
		}else{
		    //Session::flash('msg','Failed to Disconnect CID-'.$connection_id);
    		//return redirect('automation');
    		//return back()->withErrors(['Failed to Disconnect !!!']);
		}
	}
	
	public function device_wise_disconnect($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
		$to_date = date("Y-m-d");
		$device_id = $id;
		$connection_result = DB::select("SELECT connection_list.connection_id,connection_list.limit_number,connection_list.limit_type,connection_list.client_id,connection_list.ppoe_user,invoice.invoice_created_date FROM connection_list,invoice WHERE connection_list.link_status=1 and connection_list.device_id = $device_id and connection_list.last_cron_run != '$to_date' and connection_list.auto_disconnect=1 and connection_list.client_id=invoice.client_id and invoice.status IN (1,2,6) and invoice.invoice_created_date + interval connection_list.limit_number day<'$to_date' GROUP by connection_list.connection_id");
		
		$prepaid_connection_result = DB::select("SELECT connection_list.connection_id,connection_list.limit_number,connection_list.limit_type,connection_list.client_id,connection_list.ppoe_user,invoice.invoice_created_date FROM connection_list,invoice WHERE connection_list.link_status=1 and connection_list.device_id = $device_id and connection_list.last_cron_run != '$to_date' and connection_list.auto_disconnect=1 and connection_list.client_id=invoice.client_id and invoice.status IN (1,2,6) and invoice.invoice_id in (SELECT invoice_id FROM invoice WHERE invoice_recurring_id in (SELECT invoice_id FROM invoice_recurring where frequency='p1')) GROUP by connection_list.connection_id");

		$device = DB::table('device_settings')->where('id',$device_id)->first();
		
		$last_cron_run = array(
			'last_cron_run' 	=> date('Y-m-d')
		);
		
		include_once(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = false;
        $i=0; 		
		if($device && $API->connect($device->device_ip, $device->username, $device->password)){
            $all_connections = array();
            foreach($connection_result as $value){
                $all_connections[$value->connection_id] = $value;
            }
            foreach($prepaid_connection_result as $value){
                $all_connections[$value->connection_id] = $value;
            }

            foreach($all_connections as $value){
                DB::table('billing_automation')->updateOrInsert(
                    ['connection_id' => $value->connection_id],
                    [
                        'admin_id' => 0,
                        'client_id' => $value->client_id,
                        'connection_id' => $value->connection_id,
                        'nat_add_time' => date("Y-m-d H:i:s"),
                        'note' => "Automation",
                        'status' => 1,
                    ]
                );

                $ip_result = DB::select("SELECT * FROM connection_ip WHERE connection_ip.connection_id = $value->connection_id");
                $disconnectApplied = false;

                if(!empty($ip_result)){
                    foreach($ip_result as $val){
                        try {
                            $API->comm("/ip/firewall/address-list/add\n=list=payment_reminder\n=address=".$val->ip_address."\n=comment=ILC-".$value->client_id."__CID-".$value->connection_id);
                            if($val->type == 'ipv_six'){
                                $API->comm("/ipv6/firewall/address-list/add\n=list=payment_reminder\n=address=".$val->ip_address."\n=comment=ILC-".$value->client_id."__CID-".$value->connection_id);
                            }
                            Log::info('DISCONNECT_SUCCESS method=ip connection_id='.$value->connection_id.' device_id='.$device_id.' ip='.$val->ip_address);
                            $disconnectApplied = true;
                        } catch (\Throwable $e) {
                            Log::error('DISCONNECT_FAIL method=ip connection_id='.$value->connection_id.' device_id='.$device_id.' ip='.$val->ip_address.' reason='.$e->getMessage());
                            Log::error('Disconnect apply failed for CID-'.$value->connection_id.' on device-'.$device_id.': '.$e->getMessage());
                        }
                    }
                }

                // PPOE user disconnect (disable secret) while keeping old IP logic untouched.
                $ppoeDisconnected = $this->applyPppoeDisconnect($API, $value->ppoe_user ?? '', $value->connection_id, $device_id);
                if ($ppoeDisconnected) {
                    $disconnectApplied = true;
                }

                if ($disconnectApplied) {
                    DB::table('connection_list')->where('connection_id',$value->connection_id)->update($last_cron_run);
                } else {
                    Log::warning('Disconnect not applied for CID-'.$value->connection_id.' on device-'.$device_id);
                }

                $i++;
            }
            $API->disconnect();
		} else {
            Log::error('Device connection failed for device-'.$device_id.' while running device_wise_disconnect');
		}
		
		$cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = "";
		if($id==1){
		    $update = DB::table('cron_settings')->where('cron_id',9)->update($cron_log);
		}
		if($id==2){
		    $update = DB::table('cron_settings')->where('cron_id',10)->update($cron_log);
		}
		if($id==3){
		    $update = DB::table('cron_settings')->where('cron_id',11)->update($cron_log);
		}
		if($id==5){
		    $update = DB::table('cron_settings')->where('cron_id',19)->update($cron_log);
		}
		
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
		

	}

    public function device_wise_disconnect_all()
    {
        $lockName = 'cron:device_wise_disconnect_all';
        $lock = DB::selectOne("SELECT GET_LOCK(?, 0) AS l", [$lockName]);
        if (!$lock || (int)$lock->l !== 1) {
            echo 'Device disconnect all cron is already running';
            return;
        }

        try {
        $device_ids = DB::table('connection_list')
            ->select('device_id')
            ->where('link_status', 1)
            ->where('auto_disconnect', 1)
            ->whereNotNull('device_id')
            ->groupBy('device_id')
            ->orderBy('device_id')
            ->pluck('device_id');

        $count = 0;
        $success = 0;
        $failed = 0;
        foreach ($device_ids as $device_id) {
            $count++;
            try {
                $this->device_wise_disconnect((int) $device_id);
                $success++;
            } catch (\Throwable $e) {
                $failed++;
                Log::error('device_wise_disconnect_all failed for device-'.$device_id.': '.$e->getMessage());
            }
        }

        $line = "DIAGNOSTIC cron=device_wise_disconnect_all total_devices=".$count." success=".$success." failed=".$failed;
        echo "Total Device Processed - ".$count;
        echo "<br>".$line;
        Log::info($line);
        } finally {
            DB::select("SELECT RELEASE_LOCK(?)", [$lockName]);
        }
    }
	
	public function device_wise_ppoe_setup($id = NULL){
	    
	    date_default_timezone_set('Asia/Dhaka');
		$to_date = date("Y-m-d");
		$device_id = $id;
		
        
		$device = DB::table('device_settings')->where('id',$device_id)->first();
		
		
		
		include_once(app_path() . '/NatApi/routeros_api.class.php');
		$API = new \RouterosAPI();
        $API->debug = false;
        $i=0; 		
		if($device){
    		
    			
    			if ($API->connect($device->device_ip, $device->username, $device->password)) {
                				        
                				        
                				        $name = "4705Zayed";
                				        $password = "4705Zayed";
                				        $service = "pppoe";
                				        $profile = "30 Mbps Zayed";
                				        $local_address = "172.21.60.1";
                				        
                				        $rate_limit="35M/32M";
                				        $dns_server = '1.1.1.1';
                				        $dns_server_2 = '8.8.8.8';
                				        
                				        $remote_address = "172.21.60.117";
                				        
                				        $id = "0";
                				        
                				        
                				        //$API->comm("/ppp/secret/add\n=name=".$name."\n=password=".$password."\n=service=".$service."\n=profile=".$profile."\n=local-address=".$local_address."\n=remote-address=".$remote_address."\n=comment=ILC-");
                				        //$API->comm("/ppp/secret/disable\n=numbers=".$name);remove
                				        //$API->comm("/ppp/secret/enable\n=numbers=".$name);
                				        
                				        // $API->write("/ppp/secret/print");
                            //             $ARRAYS = $API->read();
                                        
                            //             //print_r($ARRAYS);
                                        
                                        
                            //             foreach ($ARRAYS as $secret) {
                            //                 // if($secret['name'] ==  $name){
                            //                 //     $id = $secret['.id'];
                            //                 // }
                                            
                                            
                            //                 $ppoe_profile = DB::table('ppoe_profile')->where('profile',$secret['profile'])->first();
                                            
                            //                 echo $secret['name'];
                                            
                            //                 $router_ppoe_table = array(
                            //                     'name' 			=> $secret['name'],
                            //                     'password'          => $secret['password'],
                            //                     'profile_id'        => $ppoe_profile->id,
                            //                     'local_address'     => $secret['local-address'],
                            //                     'remote_address'  => $secret['remote-address'],
                            //                     'device_id' 				=> $device_id,
                            //                 );
                                            
                            //                 $ppoe_user_list = DB::table('router_ppoe_list')->where('name',$secret['name'])->first();
                            //                 if(empty($ppoe_user_list)){
                            //                     $insert_id = DB::table('router_ppoe_list')->insertGetId($router_ppoe_table);
                            //                 //echo $secret['name'];
                            //                 }
                                             
                                            
                            //                 //exit();
                            //             }
                                        
                            //             $API->comm("/ppp/secret/remove\n=numbers=". $id);
                            
                            
                            //$API->comm("/ppp/profile/add\n=name=".$profile."\n=rate-limit=".$rate_limit."\n=dns-server=".$dns_server.",".$dns_server_2."\n=only-one=yes"); 
                            
                             
                            //ppp profile set [find name="profile_name"] rate-limit="download/upload"  
                            
                            $API->write("/ppp/profile/print");
                                        $ARRAYS = $API->read();
                                        
                                        print_r($ARRAYS);
                                        exit();
                                        foreach ($ARRAYS as $secret) {
                                            if($secret['name'] ==  $profile){
                                                $id = $secret['.id'];
                                            }
                                        }
                            
                            //$API->comm("/ppp/profile/set\n=numbers=".$id."\n=rate-limit=".$rate_limit);
                            // $API->comm("/ppp/profile/remove\n=numbers=".$id);
                            //ppp profile remove [find name="profile_name"]
                            //ppp profile add name="5Mbps_2Mbps_Profile"  rate-limit="5M/2M"
                                        
                				        // ppp secret print detail brief
                				        echo "found";
                				        exit();
                				        
                				    }
		}
		
		
		$update = "";
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
	}
	
	function recurring_invoice_create_cron_new(){
       
       date_default_timezone_set('Asia/Dhaka');
        $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        //$todayDate = date('j/n/Y');
        $todayDate = '01/12/2025';
        //echo $todayDate;die;
        // echo date('Y-m-d H:i:a');
        // exit();
        //$todayDay = date('j');
        $todayDay = 01;
        $todayMonth = date('n');
        //$todayYear = date('Y');
        $todayYear = '2025';
        $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
        
        $dayArray = array();
        $lastDateOfTheMonth = 0;
        if(intval($todayDay) == intval($totalDaysOfTheMonth)){
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                $dayArray[] = $i;
            }
        }else{
            $dayArray[] = $todayDay;
        }
        
        $currentMonth = date('m'); 
        $currentYear = date('Y');
        
        /*1 month*/
        $monthlyRecurring = DB::table('invoice_recurring')
                            ->where('frequency', 'm1')
                            ->where('status', 1)
                            ->where(function ($query) use ($currentMonth, $currentYear) {
                                $query->where(function ($q) use ($currentMonth, $currentYear) {
                                    // Condition-1: last_invoice_id ache and date current month na
                                    $q->whereNotNull('last_invoice_id')
                                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                                      });
                                })
                                ->orWhere(function ($q) {
                                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                                    $q->whereNull('last_invoice_id')
                                      ->whereNull('last_invoice_created_date');
                                });
                            })
                            ->get();
            
        if($monthlyRecurring){
            $i = 0;
            foreach($monthlyRecurring as $invoice){
                if(!empty($invoice->starting_date)){
                    if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                    }else{
                        $startingDate_time = date('U', strtotime($invoice->starting_date));
                        $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                        $TodayDateString = $myDateTime->format('U');
                        if($startingDate_time <= $TodayDateString){
                            $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                            $startingDay = date('j', strtotime($invoice->starting_date));
    
                            if(in_array($startingDay, $dayArray)){
                                
                                $current_month = date('m');
        						$current_year = date('Y');
        						$check_bill = DB::select("SELECT invoice_id,client_id,invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id'");
        						if($check_bill)
        						{
        						    //$i++;
        						    //echo $i.' ='.$invoice->starting_date.'= 1 months - '.$invoice->invoice_id.' Created<br>';
        						    //$this->auto_create_invoice($invoice->invoice_id);
                                    //$month1++;
        						}else{
        						   $i++;
        						   //$this->auto_create_invoice($invoice->invoice_id);
                                   $month1++;
        						   echo $i.' ='.$invoice->starting_date.'= 1 months - '.$invoice->invoice_id.' not created <br>';
        						}
                                
                            }
                        }
                    }
                }
                /*else{
                    if($lastDateOfTheMonth){
                        $this->invoice_recurring_model->auto_create_invoice($invoice->invoice_id);
                    }
                }*/
            }
        }
        /*2 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m2')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 2 == 0) {
                                echo '2 months - '.$invoice->invoice_id.'<br>';
                                //$this->auto_create_invoice($invoice->invoice_id);
                                //$month2++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*3 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m3')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 3 == 0) {
                                echo '3 months - '.$invoice->invoice_id.'<br>';
                                //$this->auto_create_invoice($invoice->invoice_id);
                            	//$month3++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*6 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m6')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 6 == 0) {
                                echo '6 months - '.$invoice->invoice_id.'<br>';
                                //$this->auto_create_invoice($invoice->invoice_id);
                            	//$month6++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*1 year*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y1')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        echo '01 years - '.$invoice->invoice_id.'<br>';
                        //$this->auto_create_invoice($invoice->invoice_id);
                    	//$year1++;
                    }
                }
            }
        }
        }
        /*2 years*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y2')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 2 == 0) {
                            echo '02 years - '.$invoice->invoice_id.'<br>';
                            //$this->auto_create_invoice($invoice->invoice_id);
                            //$year2++;
                        }
                    }
                }
            }
        }
        }
        /*3 years*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y3')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 3 == 0) {
                            echo '03 years - '.$invoice->invoice_id.'<br>';
                            //$this->auto_create_invoice($invoice->invoice_id);
                            //$year3++;	
                        }
                    }
                }
            }
        }
        }
        
        echo 'cron runned successfully...';
//         $send_email = true;
// 		if($send_email) {
		    
// 		    $type = 'Cron Mail';
// 	        $subject = 'Recurring Billing Cron';
// 	        $from_email = 'info@infolinkbd.com';
			
// 			$emails = ['ne.zayed@gmail.com'];
			
// 		    $data = array('month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
// 		    echo 'Month- '.$month1;
// 		    echo '<br>Month2- '.$month2;
// 		    echo '<br>Month3- '.$month3;
// 		    echo '<br>Month6- '.$month6;
// 		    echo '<br>Year1- '.$year1;
// 		    echo '<br>Year2- '.$year2;
// 		    echo '<br>Year 3- '.$year3;
//             Mail::send('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
//                  $message->to(['shahrussell25@gmail.com','info@sakifahmed.me'], 'Cron Mail')->bcc('ne.zayed@gmail.com')->subject($subject);
         
//                  $message->from($from_email,'Infolink Limited');
//             });
			
// 		} 
        
//         $cron_log = array(
// 			'run_time' 	=> date('Y-m-d H:i:s')
// 		);
// 		$update = DB::table('cron_settings')->where('cron_id',1)->update($cron_log);
// 		if($update){
// 		    echo 'Cron Runned Successfully';
// 		}else{
// 		    echo 'Nothing to Update';
// 		}
    }
	
	//new condition added for invoice recurring table last_invoice_id, last_invoice_created_date
	public function recurring_invoice_create_cron(){
        $lockName = 'cron:recurring_invoice_create_cron';
        $lock = DB::selectOne("SELECT GET_LOCK(?, 0) AS l", [$lockName]);
        if (!$lock || (int)$lock->l !== 1) {
            echo 'Recurring cron is already running';
            return;
        }
       
        date_default_timezone_set('Asia/Dhaka');
        $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        $todayDate = date('j/n/Y');
        // echo date('Y-m-d H:i:a');
        // exit();
        $todayDay = date('j');
        $todayMonth = date('n');
        $todayYear = date('Y');
        $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
        //$totalDaysOfTheMonth =  date('t', mktime(0, 0, 0, $todayMonth, 1, $todayYear)); 
        
        $dayArray = array();
        $lastDateOfTheMonth = 0;
        if(intval($todayDay) == intval($totalDaysOfTheMonth)){
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                $dayArray[] = $i;
            }
        }else{
            $dayArray[] = $todayDay;
        }
        
        $currentMonth = date('m'); 
        $currentYear = date('Y');
        
        /*1 month*/
        //$monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m1')->where('status',1)->get();
        $monthlyRecurring = DB::table('invoice_recurring')
            ->where('frequency', 'm1')
            ->where('status', 1)
            ->where(function ($query) use ($currentMonth, $currentYear) {
                $query->where(function ($q) use ($currentMonth, $currentYear) {
                    // Condition-1: last_invoice_id ache and date current month na
                    $q->whereNotNull('last_invoice_id')
                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                      });
                })
                ->orWhere(function ($q) {
                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                    $q->whereNull('last_invoice_id')
                      ->whereNull('last_invoice_created_date');
                });
            })
            ->get();
       
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                }else{
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if($startingDate_time <= $TodayDateString){
                        $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));

                        if(in_array($startingDay, $dayArray)){
                            //echo $invoice->invoice_id.'<br>';
                                
                            $this->auto_create_invoice($invoice->invoice_id);
                            $month1++;
                            
                            echo 'm1 '.$month1.'<br/>';
                        }
                    }
                }
            }
            /*else{
                if($lastDateOfTheMonth){
                    $this->invoice_recurring_model->auto_create_invoice($invoice->invoice_id);
                }
            }*/
        }
        }
        /*2 months*/
        //$monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m2')->where('status',1)->get();
        $monthlyRecurring = DB::table('invoice_recurring')
                            ->where('frequency','m2')
                            ->where('status',1)
                            ->where(function ($query) use ($currentMonth, $currentYear) {
                                $query->where(function ($q) use ($currentMonth, $currentYear) {
                                    // Condition-1: last_invoice_id ache and date current month na
                                    $q->whereNotNull('last_invoice_id')
                                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                                      });
                                })
                                ->orWhere(function ($q) {
                                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                                    $q->whereNull('last_invoice_id')
                                      ->whereNull('last_invoice_created_date');
                                });
                            })
                            ->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 2 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                                $month2++;
                                echo 'm2 '.$month2.'<br/>';
                            }
                        }
                    }
                }
            }
        }
        }
        /*3 months*/
        //$monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m3')->where('status',1)->get();
        $monthlyRecurring = DB::table('invoice_recurring')
                            ->where('frequency','m3')
                            ->where('status',1)
                            ->where(function ($query) use ($currentMonth, $currentYear) {
                                $query->where(function ($q) use ($currentMonth, $currentYear) {
                                    // Condition-1: last_invoice_id ache and date current month na
                                    $q->whereNotNull('last_invoice_id')
                                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                                      });
                                })
                                ->orWhere(function ($q) {
                                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                                    $q->whereNull('last_invoice_id')
                                      ->whereNull('last_invoice_created_date');
                                });
                            })
                            ->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 3 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                            	$month3++;
                            	echo 'm3 '.$month3.'<br/>';
                            }
                        }
                    }
                }
            }
        }
        }
        /*6 months*/
        //$monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m6')->where('status',1)->get();
        $monthlyRecurring = DB::table('invoice_recurring')
                            ->where('frequency','m6')
                            ->where('status',1)
                            ->where(function ($query) use ($currentMonth, $currentYear) {
                                $query->where(function ($q) use ($currentMonth, $currentYear) {
                                    // Condition-1: last_invoice_id ache and date current month na
                                    $q->whereNotNull('last_invoice_id')
                                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                                      });
                                })
                                ->orWhere(function ($q) {
                                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                                    $q->whereNull('last_invoice_id')
                                      ->whereNull('last_invoice_created_date');
                                });
                            })
                            ->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 6 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                            	$month6++;
                            	echo 'm6 '.$month6.'<br/>';
                            }
                        }
                    }
                }
            }
        }
        }
        /*1 year*/
        //$yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y1')->where('status',1)->get();
        $yearlyRecurring = DB::table('invoice_recurring')
                            ->where('frequency','y1')
                            ->where('status',1)
                            ->where(function ($query) use ($currentMonth, $currentYear) {
                                $query->where(function ($q) use ($currentMonth, $currentYear) {
                                    // Condition-1: last_invoice_id ache and date current month na
                                    $q->whereNotNull('last_invoice_id')
                                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                                      });
                                })
                                ->orWhere(function ($q) {
                                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                                    $q->whereNull('last_invoice_id')
                                      ->whereNull('last_invoice_created_date');
                                });
                            })
                            ->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $this->auto_create_invoice($invoice->invoice_id);
                    	$year1++;
                    	echo 'y1 '.$year1.'<br/>';
                    }
                }
            }
        }
        }
        /*2 years*/
        //$yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y2')->where('status',1)->get();
        $yearlyRecurring = DB::table('invoice_recurring')
                            ->where('frequency','y2')
                            ->where('status',1)
                            ->where(function ($query) use ($currentMonth, $currentYear) {
                                $query->where(function ($q) use ($currentMonth, $currentYear) {
                                    // Condition-1: last_invoice_id ache and date current month na
                                    $q->whereNotNull('last_invoice_id')
                                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                                      });
                                })
                                ->orWhere(function ($q) {
                                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                                    $q->whereNull('last_invoice_id')
                                      ->whereNull('last_invoice_created_date');
                                });
                            })
                            ->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 2 == 0) {
                            $this->auto_create_invoice($invoice->invoice_id);
                            $year2++;
                            echo 'y2 '.$year2.'<br/>';
                        }
                    }
                }
            }
        }
        }
        /*3 years*/
        //$yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y3')->where('status',1)->get();
        $yearlyRecurring = DB::table('invoice_recurring')
                            ->where('frequency','y3')
                            ->where('status',1)
                            ->where(function ($query) use ($currentMonth, $currentYear) {
                                $query->where(function ($q) use ($currentMonth, $currentYear) {
                                    // Condition-1: last_invoice_id ache and date current month na
                                    $q->whereNotNull('last_invoice_id')
                                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                                      });
                                })
                                ->orWhere(function ($q) {
                                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                                    $q->whereNull('last_invoice_id')
                                      ->whereNull('last_invoice_created_date');
                                });
                            })
                            ->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 3 == 0) {
                            $this->auto_create_invoice($invoice->invoice_id);
                            $year3++;	
                            echo 'y3 '.$year3.'<br/>';
                        }
                    }
                }
            }
        }
        }
        
        $createdTotal = (int) $month1 + (int) $month2 + (int) $month3 + (int) $month6 + (int) $year1 + (int) $year2 + (int) $year3;
        $send_email = $createdTotal > 0;
		if($send_email) {
		    
		    $type = 'Cron Mail';
	        $subject = 'Recurring Billing Cron';
	        $from_email = 'info@infolinkbd.com';
			
			$emails = ['ne.zayed@gmail.com'];
			
		    $data = array('month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
		    echo 'Month- '.$month1;
		    echo '<br>Month2- '.$month2;
		    echo '<br>Month3- '.$month3;
		    echo '<br>Month6- '.$month6;
		    echo '<br>Year1- '.$year1;
		    echo '<br>Year2- '.$year2;
		    echo '<br>Year 3- '.$year3;
            $this->safeMailSend('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                 $message->to(['shahrussell25@gmail.com','info@sakifahmed.me'], 'Cron Mail')->bcc('ne.zayed@gmail.com')->subject($subject);
         
                 //$message->to($emails, $type)->subject($subject);
                 $message->from($from_email,'Infolink Limited');
            }, 'recurring_invoice_create_cron');
			
		} else {
            echo 'No recurring invoice created in this run. Email skipped.<br>';
        } 
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',1)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
        $this->emitDiagnosticLine('recurring_invoice_create_cron');
        DB::select("SELECT RELEASE_LOCK(?)", [$lockName]);
    }
    
    

    public function recurring_invoice_create_cron_niloy() {
        date_default_timezone_set('Asia/Dhaka');
        $today = new DateTime();
        $todayDate = $today->format('Y-m-d');
        $todayDay = (int) $today->format('j');
        $todayMonth = (int) $today->format('n');
        $todayYear = (int) $today->format('Y');
        $totalDaysOfMonth = cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
    
        $dayArray = ($todayDay === $totalDaysOfMonth)
            ? range($todayDay, 31)
            : [$todayDay];
    
        $counters = [
            'm1' => 0, 'm2' => 0, 'm3' => 0,
            'm6' => 0, 'y1' => 0, 'y2' => 0, 'y3' => 0
        ];
    
        $frequencies = [
            'm1' => 1,
            'm2' => 2,
            'm3' => 3,
            'm6' => 6,
            'y1' => 1,
            'y2' => 2,
            'y3' => 3
        ];
    
        foreach ($frequencies as $freq => $interval) {
            $invoices = DB::table('invoice_recurring')
                ->where('frequency', $freq)
                ->where('status', 1)
                ->where(function ($query) use ($todayMonth, $todayYear) {
                    $query->where(function ($q) use ($todayMonth, $todayYear) {
                        $q->whereNotNull('last_invoice_id')
                          ->where(function ($q2) use ($todayMonth, $todayYear) {
                              $q2->whereMonth('last_invoice_created_date', '!=', $todayMonth)
                                 ->orWhereYear('last_invoice_created_date', '!=', $todayYear);
                          });
                    })
                    ->orWhere(function ($q) {
                        $q->whereNull('last_invoice_id')
                          ->whereNull('last_invoice_created_date');
                    });
                })
                ->get();
    
            foreach ($invoices as $invoice) {
                if (empty($invoice->starting_date)) continue;
                if ($invoice->number_of_invoice != 0 && $invoice->number_of_created_invoice >= $invoice->number_of_invoice) continue;
    
                $startDate = new DateTime($invoice->starting_date);
                if ($startDate > $today) continue;
    
                $startDay = (int) $startDate->format('j');
                if (!in_array($startDay, $dayArray)) continue;
    
                $intervalType = substr($freq, 0, 1); // 'm' or 'y'
                $intervalValue = $interval;
    
                $monthsDiff = ((int) $today->format('Y') - (int) $startDate->format('Y')) * 12 + ((int) $today->format('n') - (int) $startDate->format('n'));
                $yearsDiff = (int) $today->format('Y') - (int) $startDate->format('Y');
    
                if (
                    ($intervalType === 'm' && $monthsDiff % $intervalValue === 0) ||
                    ($intervalType === 'y' && $yearsDiff % $intervalValue === 0 && $startDate->format('j/n') === $today->format('j/n'))
                ) {
                    try {
                        $this->auto_create_invoice($invoice->invoice_id);
                        $counters[$freq]++;
                        echo "$freq {$counters[$freq]}<br/>";
                    } catch (Exception $e) {
                        Log::error("Invoice generation failed for ID {$invoice->invoice_id}: " . $e->getMessage());
                    }
                }
            }
        }
    
        $this->sendCronReport($counters);
    
        $updated = DB::table('cron_settings')->where('cron_id', 1)->update([
            'run_time' => date('Y-m-d H:i:s')
        ]);
    
        $this->emitDiagnosticLine('recurring_invoice_create_cron');
        echo $updated ? 'Cron Runned Successfully' : 'Nothing to Update';
    }
    
    private function sendCronReport(array $counters) {
        $subject = 'Recurring Billing Cron - ' . date('Y-m-d H:i:s');
        $from_email = 'info@infolinkbd.com';
        $to = ['shahrussell25@gmail.com', 'info@sakifahmed.me'];
        $bcc = ['ne.zayed@gmail.com'];
    
        $this->safeMailSend('mail_template.billing_cron_run_email', $counters, function ($message) use ($to, $bcc, $subject, $from_email) {
            $message->to($to, 'Cron Mail')
                    ->bcc($bcc)
                    ->subject($subject)
                    ->from($from_email, 'Infolink Limited');
        }, 'sendCronReport');
    
        foreach ($counters as $key => $count) {
            echo ucfirst($key) . ": $count<br/>";
        }
    }

    
    
    //this cron before 30-06-2025
    function recurring_invoice_create_cronOld(){
       
       date_default_timezone_set('Asia/Dhaka');
        $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        $todayDate = date('j/n/Y');
        // echo date('Y-m-d H:i:a');
        // exit();
        $todayDay = date('j');
        $todayMonth = date('n');
        $todayYear = date('Y');
        $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
        //$totalDaysOfTheMonth =  date('t', mktime(0, 0, 0, $todayMonth, 1, $todayYear)); 
        
        $dayArray = array();
        $lastDateOfTheMonth = 0;
        if(intval($todayDay) == intval($totalDaysOfTheMonth)){
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                $dayArray[] = $i;
            }
        }else{
            $dayArray[] = $todayDay;
        }
        
        
        
        /*1 month*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m1')->where('status',1)->get();
        //$monthlyRecurring = DB::select("SELECT * FROM invoice_recurring WHERE status = 1 and frequency='m1' AND starting_date < '2024-12-02' and invoice_id not in (SELECT invoice_recurring_id FROM invoice WHERE invoice_created_date = '2024-12-01 00:00:00')");
		
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                }else{
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if($startingDate_time <= $TodayDateString){
                        $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));

                        if(in_array($startingDay, $dayArray)){
                            //echo $invoice->invoice_id.'<br>';
                                
                            $this->auto_create_invoice($invoice->invoice_id);
                            $month1++;
                        }
                    }
                }
            }
            /*else{
                if($lastDateOfTheMonth){
                    $this->invoice_recurring_model->auto_create_invoice($invoice->invoice_id);
                }
            }*/
        }
        }
        /*2 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m2')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 2 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                                $month2++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*3 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m3')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 3 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                            	$month3++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*6 months*/
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m6')->where('status',1)->get();
        if($monthlyRecurring){
        foreach($monthlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('U', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $TodayDateString = $myDateTime->format('U');
                    if ($startingDate_time <= $TodayDateString) {
                        $startingDate = date('Y-m-00', strtotime($invoice->starting_date));
                        $startingDay = date('j', strtotime($invoice->starting_date));
                        if (in_array($startingDay, $dayArray)) {
                            $date1 = date_create($myDateTime->format('Y-m-00'));
                            $date2 = date_create($startingDate);
                            $diff12 = date_diff($date1, $date2);
                            $months = (intval($diff12->y) * 12) + intval($diff12->m);
                            if ($months % 6 == 0) {
                                $this->auto_create_invoice($invoice->invoice_id);
                            	$month6++;
                            }
                        }
                    }
                }
            }
        }
        }
        /*1 year*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y1')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $this->auto_create_invoice($invoice->invoice_id);
                    	$year1++;
                    }
                }
            }
        }
        }
        /*2 years*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y2')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 2 == 0) {
                            $this->auto_create_invoice($invoice->invoice_id);
                            $year2++;
                        }
                    }
                }
            }
        }
        }
        /*3 years*/
        $yearlyRecurring = DB::table('invoice_recurring')->where('frequency','y3')->where('status',1)->get();
        if($yearlyRecurring){
        foreach($yearlyRecurring as $invoice){
            if(!empty($invoice->starting_date)){
                if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){

                }else {
                    $startingDate_time = date('j/n', strtotime($invoice->starting_date));
                    $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                    $newDateString = $myDateTime->format('j/n');
                    if ($startingDate_time == $newDateString) {
                        $startingDate = date('Y-00-00', strtotime($invoice->starting_date));
                        $date1 = date_create($myDateTime->format('Y-00-00'));
                        $date2 = date_create($startingDate);
                        $diff12 = date_diff($date1, $date2);
                        $years = intval($diff12->y);
                        if ($years % 3 == 0) {
                            $this->auto_create_invoice($invoice->invoice_id);
                            $year3++;	
                        }
                    }
                }
            }
        }
        }
        
        $send_email = true;
		if($send_email) {
		    
		    $type = 'Cron Mail';
	        $subject = 'Recurring Billing Cron';
	        $from_email = 'info@infolinkbd.com';
			
			$emails = ['ne.zayed@gmail.com'];
			
		    $data = array('month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
		    echo 'Month- '.$month1;
		    echo '<br>Month2- '.$month2;
		    echo '<br>Month3- '.$month3;
		    echo '<br>Month6- '.$month6;
		    echo '<br>Year1- '.$year1;
		    echo '<br>Year2- '.$year2;
		    echo '<br>Year 3- '.$year3;
            $this->safeMailSend('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                 $message->to(['shahrussell25@gmail.com','info@sakifahmed.me'], 'Cron Mail')->bcc('ne.zayed@gmail.com')->subject($subject);
         
                 //$message->to($emails, $type)->subject($subject);
                 $message->from($from_email,'Infolink Limited');
            }, 'recurring_invoice_create_cronOld');
			
		} 
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',1)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
    }
    
    function recurring_prepaid_invoice_create_cron(){
        $lockName = 'cron:recurring_prepaid_invoice_create_cron';
        $lock = DB::selectOne("SELECT GET_LOCK(?, 0) AS l", [$lockName]);
        if (!$lock || (int)$lock->l !== 1) {
            echo 'Prepaid recurring cron is already running';
            return;
        }
       
       try {
       date_default_timezone_set('Asia/Dhaka');
        $prepaid1 = $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        
        $today = date('Y-m-d');
        //$prepaidRecurring = DB::table('invoice_recurring')->where('frequency','p1')->where('status',1)->where('starting_date','<=',$today)->get();
		$prepaidRecurring = DB::select("SELECT * FROM invoice_recurring WHERE frequency= 'p1' and status='1' and DATE_FORMAT(starting_date,'%Y-%m-%d') <= '".date('Y-m-d')."' and invoice_id not in (SELECT invoice_recurring_id FROM `invoice` WHERE status in ('1','0') and frequency='p1')");
		
        // print_r($prepaidRecurring);
        // echo count($prepaidRecurring);
        // exit();
        if($prepaidRecurring){
        foreach($prepaidRecurring as $invoice){
                            
            $starting_date = date('Y-m-d', strtotime($invoice->starting_date));
            $today = date('Y-m-d');
                        
            $last_bill_pay = DB::table('payment')->where('client_id',$invoice->client_id)->where('payment_date',$today)->get();
            $due_invoice_check = DB::table('invoice')->where('client_id',$invoice->client_id)->where('invoice_recurring_id',$invoice->invoice_id)->whereIn('status',['1','0'])->get();
            
            $last_invoice_create = DB::table('invoice')->where('client_id',$invoice->client_id)->where('invoice_recurring_id',$invoice->invoice_id)->whereIn('status',['1','0','2','3'])->orderBy('invoice_created_date', 'desc')->first();
            
            //print_r($last_invoice_create);
            //echo $last_invoice_create->invoice_created_date;
            if($last_invoice_create){
                $last_invoice_date = date('Y-m-d', strtotime($last_invoice_create->invoice_created_date. ' + 30 days'));
            }else{
                $last_invoice_date = $today;
            }
            // echo $last_invoice_date;
            // exit();
            
            if(!$last_bill_pay->isEmpty()){
                echo "Not payment empty ";
                echo $invoice->client_id.'=============<br/>';
                //exit();
                                
            }else{
                
                // echo $invoice->client_id;
                //             exit();
                    if($last_invoice_date <= $today){
                                $last_payment_invoice = DB::table('payment')
                                    ->where('client_id',$invoice->client_id)
                                    ->orderBy('payment_date', 'desc')
                                    ->first();
                                if($last_invoice_create && !empty($last_payment_invoice)){
                                    $last_payment_invoice_date = date('Y-m-d', strtotime($last_payment_invoice->payment_date. ' + 30 days'));
                                }else{
                                    $last_payment_invoice_date = $today;
                                }
                                // echo $last_payment_invoice_date;
                                // exit();
                                if($last_payment_invoice_date <= $today){
                                    echo 'create invoice ';
                                    echo $invoice->client_id."<br>";
                                    echo $starting_date.' - '.$today.'=============<br/>';
                                    $created_id = $this->auto_create_invoice($invoice->invoice_id);
                                    echo $created_id.'---'.$invoice->invoice_id.'=============<br/>';
                                    $prepaid1++;
                                }
                    }else{
                        echo "Not payment empty ";
                    echo $invoice->client_id.'=============<br/>';
                        
                    }
            }
            
            
            
        }
        }
        
        echo $prepaid1;
        
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		$update = DB::table('cron_settings')->where('cron_id',16)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
        $this->emitDiagnosticLine('recurring_prepaid_invoice_create_cron');
        return;
        
        $send_email = true;
		if($send_email) {
		    
		    $type = 'Cron Mail';
	        $subject = 'Prepaid Recurring Billing Cron';
	        $from_email = 'info@infolinkbd.com';
			
			$emails = ['ne.zayed@gmail.com'];
			
		    $data = array('Prepaid'=>$prepaid1,'month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
		    echo 'Prepaid- '.$prepaid1;
            $this->safeMailSend('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                 $message->to($emails, $type)->subject($subject);
                 $message->from($from_email,'Infolink Limited');
            }, 'recurring_prepaid_invoice_create_cron');
			
		} 
         
        
        } finally {
            DB::select("SELECT RELEASE_LOCK(?)", [$lockName]);
        }
    }
    
    function recurring_prepaid_invoice_create_cron_old(){
       
       date_default_timezone_set('Asia/Dhaka');
        $prepaid1 = $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        $todayDate = date('j/n/Y');
        // echo date('Y-m-d H:i:a');
        // exit();
        $todayDay = date('j');
        $todayMonth = date('n');
        $todayYear = date('Y');
        $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
        
        $dayArray = array();
        $lastDateOfTheMonth = 0;
        if(intval($todayDay) == intval($totalDaysOfTheMonth)){
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                $dayArray[] = $i;
            }
        }else{
            $dayArray[] = $todayDay;
        }
        
        $today = date('Y-m-d');
        /*1 month*/
        $prepaidRecurring = DB::table('invoice_recurring')->where('frequency','p1')->where('status',1)->where('starting_date','<=',$today)->get();
        // print_r($prepaidRecurring);
        // echo count($prepaidRecurring);
        // exit();
        if($prepaidRecurring){
        foreach($prepaidRecurring as $invoice){
            
            //$due_invoice_check = DB::table('invoice')->where('client_id',$invoice->client_id)->where('invoice_recurring_id',$invoice->invoice_id)->where('status',1)->get();
            $due_invoice_check = DB::table('invoice')->where('client_id',$invoice->client_id)->where('invoice_recurring_id',$invoice->invoice_id)->whereIn('status',['1','0'])->get();
            if(!$due_invoice_check->isEmpty()){
                //echo $invoice->client_id."<br>";
            }else{
                ////echo $invoice->client_id."not fount<br>";
                if(!empty($invoice->starting_date)){
                    if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                        
                    }else{
                        // $startingDate_time = date('U', strtotime($invoice->starting_date));
                        // $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                        // $TodayDateString = $myDateTime->format('U');
                        
                        $starting_date = date('Y-m-d', strtotime($invoice->starting_date));
                        $today = date('Y-m-d');
                        
                        $last_bill_pay = DB::table('payment')->where('client_id',$invoice->client_id)->where('payment_date',$today)->get();
                        
                        // print_r($last_bill_pay);
                        // exit();
                        
                        if($last_bill_pay->isEmpty()){
                            echo $invoice->client_id;
                            exit();
                            if ($starting_date <= $today) {
                                echo 'create invoice ';
                                echo $invoice->client_id."<br>";
                                echo $starting_date.' - '.$today.'=============<br/>';
                                $created_id = $this->auto_create_invoice($invoice->invoice_id);
                                echo $created_id.'---'.$invoice->invoice_id;
                            }else{
                                // echo 'date not match';
                                // echo $starting_date.' - '.$today.'=============<br/>';
                            }
                        }
                        
                        
                        // if($startingDate_time <= $TodayDateString){
                        //     $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                        //     $startingDay = date('j', strtotime($invoice->starting_date));
    
                        //     if(in_array($startingDay, $dayArray)){
                        //         echo $invoice->client_id."<br>";
                        //         //$this->auto_create_invoice($invoice->invoice_id);
                        //         $month1++;
                        //     }
                        // }
                    }
                }
            }
            
            //echo $invoice->client_id.'<br>';
            
            
            
        }
        }
        
        
        
        exit();
        
        $send_email = true;
		if($send_email) {
		    
		    $type = 'Cron Mail';
	        $subject = 'Prepaid Recurring Billing Cron';
	        $from_email = 'info@infolinkbd.com';
			
			$emails = ['ne.zayed@gmail.com'];
			
		    $data = array('Prepaid'=>$prepaid1,'month1'=>$month1,'month2'=>$month2,'month3'=>$month3,'month6'=>$month6,'year1'=>$year1,'year2'=>$year2,'year3'=>$year3);
		    echo 'Prepaid- '.$prepaid1;
            $this->safeMailSend('mail_template.billing_cron_run_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                 $message->to($emails, $type)->subject($subject);
                 $message->from($from_email,'Infolink Limited');
            }, 'recurring_prepaid_invoice_create_cron_old');
			
		} 
         
        $cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
// 		$update = DB::table('cron_settings')->where('cron_id',1)->update($cron_log);
// 		if($update){.
// 		    echo 'Cron Runned Successfully';
// 		}else{
// 		    echo 'Nothing to Update';
// 		}
    }
    
    public function auto_create_invoice($recurring_id = NULL)
    {
        if(!$recurring_id){
            return;
        }
        
        $today = date('Y-m-d');
        DB::beginTransaction();
        try {
        $data = DB::table('invoice_recurring')
            ->where('invoice_id', $recurring_id)
            ->lockForUpdate()
            ->first();
        
        if(!$data){
            DB::rollBack();
            return;
        }

        $alreadyCreated = DB::table('invoice')
            ->where('invoice_recurring_id', $recurring_id)
            ->whereDate('invoice_created_date', $today)
            ->lockForUpdate()
            ->exists();

        if($alreadyCreated){
            DB::commit();
            return;
        }

        $invoice_table = array(
            'client_id' 			=> $data->client_id,
            'company_name'          => $data->company_name,
            'vat_reg_number'        => $data->vat_reg_number,
            'client_address_id'     => $data->client_address_id,
            'invoice_recurring_id'  => $recurring_id,
            'group_id' 				=> $data->group_id,
            'recurring_id'          => $recurring_id,
            'created_by' 			=> $data->admin_id,
            'invoice_created_date' 	=> date('Y-m-d'),
            'status' 				=> 1,
            'vat_status' 			=> $data->vat_status,
            'frequency' 			=> $data->frequency,
            'discount' 				=> $data->discount,
            'bill_period' 			=> $this->get_bill_period($data->bill_period),
            'note'                  => $data->note,
            'reference_by'	=> $data->reference_by,
            'referred_employee_id'	=> $data->referred_employee_id,
            'referred_employee_commission' => $data->referred_employee_commission
        );
        $insert_id = DB::table('invoice')->insertGetId($invoice_table);
        $i = 0;
        $new_number = 0;
        if($data->number_of_invoice > 0) {
            $number_of_invoice = $data->number_of_created_invoice;
            $new_number = intval($number_of_invoice) + 1;
            
            $data_update = array(
                            'number_of_created_invoice'=>$new_number
                            );
            DB::table('invoice_recurring')->where('invoice_id',$data->invoice_id)->update($data_update);
            
        }
        if($data->number_of_invoice != 0 && $data->number_of_invoice <= $new_number){
            $data_update = array(
                            'number_of_created_invoice'=> 0,
                            'status'=> 0
                            );
            DB::table('invoice_recurring')->where('invoice_id',$data->invoice_id)->update($data_update);
            
        }
        
        /***** for system log *****/
        $invoice_details_for_system_log = '';
        $recurring_items = DB::table('invoice_recurring_details')->where('invoice_id',$recurring_id)->get();
        
        /***** invoice details table******/
        foreach($recurring_items as $item) {
            $invoice_details_data = array(
                'invoice_id' => $insert_id,
                'income_category_id' => $item->income_category_id,
                'package_id' => $item->package_id,
                'title' => $item->title,
                'details' => $item->details,
                'vat' => $item->vat,
                'quantity' => $item->quantity,
                'price' => $item->price,
                'invoice_recurring_details_id' => $item->invoice_details_id,
				'invoice_type' => $item->invoice_type,
				'connectivity_type' => $item->connectivity_type,
				'download_speed' => $item->download_speed,
				'd_unit' => $item->d_unit,
				'upload_speed' => $item->upload_speed, 
				'u_unit' => $item->u_unit,
				'location' => $item->location
            );
            DB::table('invoice_details')->insert($invoice_details_data);

            $invoice_details_for_system_log .= 'Package Title: ' . $item->title . ', ';
            $invoice_details_for_system_log .= 'Details: ' . $item->details . ', ';
            $invoice_details_for_system_log .= 'Vat: ' . $item->vat . ', ';
            $invoice_details_for_system_log .= 'Quantity: ' . $item->quantity . ', ';
            $invoice_details_for_system_log .= 'Price: ' . $item->price . ', ';
            $i++;
        }
        
        if($data->auto_create_vat == 1){
            if($insert_id){
				$invoice_id = $insert_id;
				$vat_serial = array(
					'invoice_id' => $invoice_id,
					'admin_id' => 13
				);
				DB::table('invoice_vat_serial')->insert($vat_serial);
    		}
        }
        /*********** System Log ******************/
        $client_info = DB::table('client')->where('client_id',$data->client_id)->first();

        $system_log = array(
            'admin_id' 		=> $data->admin_id,
            'client_id' 	=> 0,
            'action'		=> 'Created',
            'description'	=>
                ',Invoice for - Client title:'.$client_info->client_title.
                ',Contact person: '.$client_info->contact_person.
                ',Invoice created date: '.date('Y-m-d').
                ',Invoice No: IL- '.$insert_id.
                ',Invoice status: '.$data->status.
                ',Discount:'.$data->discount.
                ',Bill Period:'.$invoice_table["bill_period"].
                ',Package details :'.$invoice_details_for_system_log,
            'ip_address'	=> $_SERVER['REMOTE_ADDR'] ?? 'CLI'
        );
        DB::table('system_log')->insert($system_log);
        /******* End  System Log *****************/
        //update invoice_id and date recurring table start
        $data_update = array(
                            'last_invoice_created_date'=> date('Y-m-d'),
                            'last_invoice_id' => $insert_id
                            );
        DB::table('invoice_recurring')->where('invoice_id',$data->invoice_id)->update($data_update);
        //update invoice_id and date recurring table start
        
        if($data->email_notification==1)
        {
            $invoice_id = $insert_id;
           
            $notification 	= DB::table('notification')->where('id',9)->first();
            
            $row = DB::table('invoice')
                ->join('client', 'invoice.client_id', '=', 'client.client_id')
                ->select('client.client_title','invoice.bill_period')
                ->where('invoice.invoice_id',$invoice_id)
                ->first();
           	
			$s = str_replace('[[client_title]]',$row->client_title,$notification->subject);
			
			$s1 = str_replace('[[bill_period]]',$row->bill_period,$s);
            
		    $type = $s1;
	        $subject = $s1;
	        $from_email = $notification->from_email;
			
			$emails = [$client_info->client_email1,$client_info->client_email2];
			
		    $data = array('invoice_id'=>$invoice_id);
		    
		    $c_email = array();
			if($client_info->client_email1_status != 0){
			    $emails = [$client_info->client_email1];
			    $c_email = array($client_info->client_email1);
			}elseif($client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email2];
			    $c_email = array($client_info->client_email2);
			}elseif($client_info->client_email1_status != 0 AND $client_info->client_email2_status != 0){
			    $emails = [$client_info->client_email1,$client_info->client_email2];
			    $c_email = array($client_info->client_email1,$client_info->client_email2);
			}
			
			if(!empty($c_email)){
                // Mail::send('mail_template.billing_cron_invoice_email', $data, function($message) use ($emails,$type,$subject,$from_email) {
                //      $message->to($emails, $type)->subject($subject);
                //      $message->from($from_email,'Infolink Limited');
                // });
			}
			
		
        }
        DB::commit();
        return $insert_id;
        } catch (\Throwable $e) {
            DB::rollBack();
            Log::error('auto_create_invoice failed for recurring_id '.$recurring_id.': '.$e->getMessage());
            throw $e;
        }
    }
    
    function get_bill_period($bperiod){
        $m1 = str_replace('[[current_month]]', date('F'), $bperiod);
        $m2 = str_replace('[[previous_month]]', date('F', strtotime(" -1 month ")), $m1);
        $m3 = str_replace('[[next_month]]', date('F', strtotime(" +1 month ")), $m2);
        $m4 = str_replace('[[current_year]]', date('Y'), $m3);
        $m5 = str_replace('[[previous_year]]', date('Y', strtotime("-1 year")), $m4);
        $m6 = str_replace('[[next_year]]', date('Y', strtotime("+1 year")), $m5);
        $m7 = str_replace('[[current_day]]', date('d'), $m6);
        $m8 = str_replace('[[next_day]]', date('d', strtotime("+1 day")), $m7);
        $m9 = str_replace('[[previous_day]]', date('d', strtotime("-1 day")), $m8);
        return $m9;
    }
    
    public function daily_report()
	{
		$today=date('dd/mm/yyyy');
		$invoice_sql = "SELECT inv.invoice_id,inv.created_by,inv.frequency,inv.invoice_created_date,inv.status,cl.client_title,b.branch_name,inv.bill_period,invdet.vat,invdet.quantity,invdet.price
                		FROM invoice inv
                		LEFT JOIN invoice_details invdet ON inv.invoice_id = invdet.invoice_id
                		LEFT JOIN client cl ON cl.client_id = inv.client_id
                		LEFT JOIN branch b ON b.branch_id = cl.branch_id
                		WHERE DATE_FORMAT(inv.timestamp,'%Y-%m-%d') = '".date('Y-m-d')."'";

		$invoices = DB::select($invoice_sql);

		$payment_sql = "SELECT pay.id,pay.invoice_id,pay.accept_by,pay.payment_date,pay.payment_type,pay.reference_number,pay.amount,pay.admin_id,cl.client_title,b.branch_name,inv.status AS invoice_status,pay.status AS payment_status
                		FROM payment pay
                		LEFT JOIN invoice inv ON inv.invoice_id = pay.invoice_id
                		LEFT JOIN client cl ON cl.client_id = pay.client_id
                		LEFT JOIN branch b ON b.branch_id = cl.branch_id
                		WHERE DATE_FORMAT(pay.timestamp,'%Y-%m-%d') = '".date('Y-m-d')."'";

		$payments = DB::select($payment_sql);
		
		$clients_sql = "SELECT cl.*,b.branch_name
		                FROM client as cl
		                LEFT JOIN branch b ON b.branch_id = cl.branch_id
		                WHERE DATE_FORMAT(cl.timestamp,'%Y-%m-%d') = '".date('Y-m-d')."'";
		$clients = DB::select($clients_sql);
		
		$connection_sql = "SELECT * 
		                    FROM connection_list 
		                    WHERE DATE_FORMAT(timestamp,'%Y-%m-%d') = '".date('Y-m-d')."'";
		$connections = DB::select($connection_sql);
		
		$leads_sql = "SELECT * FROM leads WHERE DATE_FORMAT(created_at,'%Y-%m-%d') = '".date('Y-m-d')."'";
		$leads = DB::select($leads_sql);
		
		$expense_sql = "SELECT * FROM expense WHERE DATE_FORMAT(clear_date,'%Y-%m-%d') = '".date('Y-m-d')."'";
		$expenses = DB::select($expense_sql);
		
		//return view("mail_template.daily_report",compact('invoices','payments','clients','connections','leads'));
		
		//$emails = ['zayed@braintreebd.com', 'shahrussell25@gmail.com'];
        $cronHealth = $this->buildDailyCronHealthSnapshot();
		$data = array(
            'invoices'=>$invoices,
            'payments'=>$payments,
            'clients'=>$clients,
            'connections'=>$connections,
            'leads'=>$leads,
            'expenses'=>$expenses,
            'cron_health'=>$cronHealth['summary'],
            'cron_recent_errors'=>$cronHealth['recent_errors'],
            'cron_recent_successes'=>$cronHealth['recent_successes'],
            'cron_disconnect_stats'=>$cronHealth['disconnect_stats'],
            'cron_reconnect_stats'=>$cronHealth['reconnect_stats'],
            'cron_lookback_hours'=>$cronHealth['lookback_hours'],
        );
        $this->safeMailSend('mail_template.daily_report', $data, function($message) {
         $message->to(['shahrussell25@gmail.com','info@sakifahmed.me'], 'Cron Mail')->bcc('ne.zayed@gmail.com')->subject('Regarding InfoLink Daily Billing Details '.date('Y-m-d'));
         $message->from('info@infolinkbd.com','Infolink Limited');
        }, 'daily_report');
        
		/*$data['run_time'] = date('Y-m-d H:i:s');
		DB::table('cron_settings')->where('cron_id',2)->update($cron_log);*/ 
		
		$cron_log = array(
			'run_time' 	=> date('Y-m-d H:i:s')
		);
		
		$update = DB::table('cron_settings')->where('cron_id',2)->update($cron_log);
		if($update){
		    echo 'Cron Runned Successfully';
		}else{
		    echo 'Nothing to Update';
		}
          
	}
	
	public function ssl_comm_admin_payment_check(){
	    
	    $link = "https://securepay.sslcommerz.com/validator/api/merchantTransIDvalidationAPI.php?tran_id=[[trn_id]]&store_id=infolinkbdlive&store_passwd=5EC4DDB3E972453074";
                
        date_default_timezone_set('Asia/Dhaka');
         
        $date_tele =  date('Y-m-d H:i:s');
        $date_tele =  date('Y-m-d H:i:s', strtotime('-10 minutes',strtotime($date_tele)));
        // echo $date_tele;
        // exit();
        
	    $due_list = DB::select("SELECT * FROM wallet_secure_payment WHERE payment_status = 1 and timestamp < '$date_tele' ORDER BY wallet_secure_payment.timestamp DESC ");
	    //echo count($due_list);die;
	    
        $i = 0;
        if($due_list){
	            foreach($due_list as $row){
	                
	                
	                
	                $api = str_replace('[[trn_id]]',"$row->tran_id",$link);
	                
	                $message = $this->executeHttpGet($api);
    				
    				
    				
    				$data = json_decode($message, true);
    				$elements = $data['element'];
    				// echo $data['no_of_trans_found'];
    				// exit();
    				
    				$in_val = 0;
    				$valid_val = 0;
    				if($data['no_of_trans_found'] > 1){
    				    while($in_val<$data['no_of_trans_found']){
    				        $status = $elements[$in_val]['status'];
    				        if($status == 'VALID' || $status == 'VALIDATED')
    				        {
    				            $valid_val = $in_val;
    				        }
    				        $in_val++;
    				    }
    				}
    				
    				//echo $valid_val;
    				$status = $elements[$valid_val]['status'];
    				// echo '<br>'.$status;
    				// exit();
    				
        // 		    $status = $elements[0]['status'];
        				
    				if($data['no_of_trans_found'] == 0){
    				    $error = $elements[$valid_val]['status'];
    				}else{
        				
        				$error = $elements[$valid_val]['error'];
    				}
    				
    				
    				
    				if($status == 'VALID' || $status == 'VALIDATED')
    				{
    				    print_r($message);
    				    echo '<br><br><br>'.$status;
    				    
    				    //exit();
    				    
    				    $bank_tran_id = $elements[$valid_val]['bank_tran_id'];
                        $base_fair = $elements[$valid_val]['base_fair'];
                        $card_brand = $elements[$valid_val]['card_brand'];
                        $card_issuer = $elements[$valid_val]['card_issuer'];
                        $card_issuer_country = $elements[$valid_val]['card_issuer_country'];
                        $card_issuer_country_code = $elements[$valid_val]['card_issuer_country_code'];
                        $card_no = $elements[$valid_val]['card_no'] ;
                        $card_sub_brand = $elements[$valid_val]['card_sub_brand'];
                        $card_type = $elements[$valid_val]['card_type'];
                        $currency = $elements[$valid_val]['currency'] ;
                        $currency_amount = $elements[$valid_val]['currency_amount'];
                        $currency_rate = $elements[$valid_val]['currency_rate'];
                        $currency_type = $elements[$valid_val]['currency_type'];
                        $risk_level = $elements[$valid_val]['risk_level'];
                        $risk_title = $elements[$valid_val]['risk_title'];
                        $store_amount = $elements[$valid_val]['store_amount'];
                        //$updated_by = '3';
                        $response = $elements;
                        
                        $tran_id = $row->tran_id;
                        
                        $update_data = array(
                            'payment_status' => 2,
                            'status'=>$status,
                            'error'=>$error,
                            'bank_tran_id'=>$bank_tran_id,
                            'card_brand'=>$card_brand,
                            'card_issuer'=>$card_issuer,
                            'card_issuer_country'=>$card_issuer_country,
                            'card_issuer_country_code'=>$card_issuer_country_code,
                            'card_no'=>$card_no,
                            'card_sub_brand'=>$card_sub_brand,
                            'card_type'=>$card_type,
                            'risk_level'=>$risk_level,
                            'risk_title'=>$risk_title,
                            'store_amount'=>$store_amount,
                            'currency'=>$currency,
                            //'updated_by'=>$updated_by,
                            'currency_type' =>$currency_type,
                            'currency_amount'=>$currency_amount,
                            'currency_rate'=>$currency_rate,
                            'base_fair'=>$base_fair,
                            'gateway_response' => $response,
                            'updated_by' => 3
                        );
                    $update = DB::table('wallet_secure_payment')->where('tran_id',$tran_id)->update($update_data);
                    if($update){
                        
                        
                        
            			if(empty($row->admin_id)){
            				
            			}else{
            			    $new_desc = "Cash Deposit By E-".$row->admin_id.". SSL-Comerce Transaction ID-".$tran_id;
                            $adjustment_data_tr = array(
                                'emp_id' => $row->admin_id,
                                'transfer_type' => '1',
                                'amount' => $row->amount,
                                'transfer_date' => date('Y-m-d'),
                                'description' => $new_desc,
                                'added_by' => $row->admin_id,
                                'added_date' => date('Y-m-d H:i:s'),
                            );
                            $insert_id = DB::table('employee_wallet')->insertGetId($adjustment_data_tr);
                            
                            $new_desc_tr = "Wallet Payment By E-".$row->admin_id.". SSL-Comerce Transaction ID-".$tran_id;
                            $adjustment_data = array(
                                'date' => date('Y-m-d'),
                                'description' => $new_desc_tr,
                                'adjustment_type' => '1',
                                'amount' => $row->amount,
                                'payment_type' => '2',
                                'account_type' => '0',
                                'account_id' => '14',
                                'admin_id' => $row->admin_id,
                                'status' => '2',
                            );
                            $insert_id = DB::table('account_adjustment')->insertGetId($adjustment_data);
                            
            			    
            			}
                        
                    }
                        
    				    continue;
    				}else{
    				    echo '<br><br><br>';
    				    print_r($message);
    				    
    				    echo '<br><br><br>'.$status;
    				    
    				    
    				    
    				    $data_update = array(
            				'payment_status' => '4',
            				'error' => $error,
            				'updated_by' => '3',
                            'gateway_response' => $elements,
            			);
                		DB::table('wallet_secure_payment')->where('tran_id',$row->tran_id)->update($data_update);
    				}
    				
    				
    				//old
    				//echo $message;
                    //if(strpos($message, "SMS SUBMITTED") !== false){
                    
                    
                    
                    
    				// if(strpos($message, "200") !== false){
        //                 echo $row->client_id." - SMS Send Done.<br>";
        //         		$i++;
            		
        //             }
    				//exit();
    				//echo $row->client_id." - SMS Send Done.<br>";
    				
    				
	                
	            }
        }
        
        
        
		
	    
	}
	
	public function payment_clear()
	{
		$today=date('dd/mm/yyyy');
		//$payment_sql = "SELECT * FROM payment WHERE payment_type in (9) and account_id in (33) AND status = 0";
		
		$payment_sql = "SELECT * FROM payment WHERE payment_type in (9) and account_id in (33) AND status = 0 AND timestamp >= NOW() - INTERVAL 2 DAY";

		$payments = DB::select($payment_sql);
		
		
		foreach($payments as $item) {
        
        $payment_id = $item->id;
	    
	    $payment_data = DB::table('payment')->where('id',$payment_id)->first();
	    
	    $invoice_id = $payment_data->invoice_id;
        
        $invoice_data = DB::table('invoice')->where('invoice_id',$invoice_id)->first();
        
        $item_list = DB::table('invoice_details')->where('invoice_id',$invoice_id)->get();
		
        $amount = 0;
		$vat = 0;
		$v = 0;
		foreach($item_list as $invoice_info)
		{
			$amount += (float)$invoice_info->price * (float)$invoice_info->quantity;
			$vat += ((float)$invoice_info->quantity * (float)$invoice_info->price * (float)$invoice_info->vat*.01);
			$v += (float)$invoice_info->vat;
		}
		$total_vat = $vat;
		$total_amount = $vat+$amount-$invoice_data->discount;
		
		$total_amount = number_format($total_amount,2);
		
        
        
        
        if($payment_data->status == $invoice_data->status){
            if($total_amount == $payment_data->amount){
                
                echo $payment_id.'-Clear<br>';
        
                $today_date = date('Y-m-d');
				$status_array = array(
					'status_clear_date'	=> $today_date,
                    'status'				 	 => '1',
                    'accept_by' 				 => '0',
                    'admin_id'   				 => '0',
				);
				DB::table('payment')->where('id',$payment_id)->update($status_array);
				
					$i_status = '3';
    		DB::table('invoice')->where('invoice_id',$invoice_id)->update(array('status'=>$i_status));
    		
            $check = DB::table('invoice_vat_serial')->where('invoice_id',$invoice_id)->first();
			if($check){
			}else{
				$client_id = $invoice_data->client_id;
			    $branch_query = DB::table('client')->select('branch_id')->where('client_id', $client_id)->first();
			    if($branch_query){
			        $branch_id = $branch_query->branch_id;
			        $branch_vat_check = DB::table('branch')->select('vat_print')->where('branch_id', $branch_id)->first();
			        if($branch_vat_check){
			            $vat_status = $branch_vat_check->vat_print;
			            if($vat_status == 1){
			                
			                $vat_serial = array(
        						'invoice_id' => $invoice_id,
        						'admin_id' => '122'
        					);
        				   DB::table('invoice_vat_serial')->insert($vat_serial);
			            }
			        }
			    }
			}
                
            }else{ echo $payment_id.'- NOT Clear (Amount)'.$total_amount.'=='.$payment_data->amount.'<br>'; }
            
            }else{ echo $payment_id.'- NOT Clear (Status)<br>'; }
        }
          
	}
	
	public function recurring_invoice_create_cron_custom(){
	    $data['result'] = '';
	    return view('recurring_form',$data);
	}
	
	public function recurring_invoice_create_cron_custom_submit(Request $request){
	    
        date_default_timezone_set('Asia/Dhaka');
        $month1 = $month2 = $month3 = $month6 = $year1 = $year2 = $year3 = 0;
        $todayDate = date('j/n/Y', strtotime($request->selected_date));
     
        $todayDay = date('n', strtotime($todayDate));
  
        $todayMonth = date('j', strtotime($todayDate));
        $todayYear = date('Y', strtotime($todayDate));
      
        $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
        
        $dayArray = array();
        $lastDateOfTheMonth = 0;
        if(intval($todayDay) == intval($totalDaysOfTheMonth)){
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                $dayArray[] = $i;
            }
        }else{
            $dayArray[] = $todayDay;
        }
        
        $monthlyRecurring = DB::table('invoice_recurring')->where('frequency','m1')->where('status',1)->get();
        
        if($monthlyRecurring){
            $i = 0;
            $created = 0;
            $notcreated = 0;
            $sameday = 0;
            $anotherday = 0;
            $timenotmatch = 0;
            foreach($monthlyRecurring as $invoice){
                if(!empty($invoice->starting_date)){
                    if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                    }else{
                        $i++;
                        $startingDate_time = date('U', strtotime($invoice->starting_date));
                        $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                        $TodayDateString = $myDateTime->format('U');
                        
                        $current_month = date('m', strtotime($invoice->starting_date));
						$current_year = date('Y', strtotime($invoice->starting_date));
                        $check_bill = DB::table('invoice')
                                    ->select('invoice_id', 'client_id', 'status', 'reason_cancel', 'invoice_created_date')
                                    ->whereMonth('invoice_created_date', $current_month)
                                    ->whereYear('invoice_created_date', $current_year)
                                    ->where('client_id', $invoice->client_id)
                                    ->first();
                        if(!empty($check_bill)){
                            //echo $i.' = RID - '.$invoice->invoice_id.' Bill Created, Invoice NO - IL-'.$check_bill->invoice_id.' Created<br>'; 
                            $created++;
                        }else{
                            //echo $i.'<b style="color:red"> = RID - '.$invoice->invoice_id.' Starting Date -'. $invoice->starting_date.' Bill Not Created </b> <br>-------------<br/>'; 
                            $notcreated++;
                            if($startingDate_time <= $TodayDateString){
                                $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                                $startingDay = date('j', strtotime($invoice->starting_date));
                                
                                $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($invoice->client_id);
                                        $client_details = '<a href="' . $clientUrl . '" target="_blank">ILC-' . $invoice->client_id . '</a>';
                                        
                                        $recurringUrl = 'https://admin.infolinkbd.com/recurring/details/' . base64_encode($invoice->invoice_id);
                                        $recurring_details = '<a href="' . $recurringUrl . '" target="_blank">RID-' . $invoice->invoice_id . '</a>';
                                
                                if(in_array($startingDay, $dayArray)){
                                    $sameday++;
                                    echo $i.'<b style="color:red"> = '.$recurring_details.' Starting Date -'. $invoice->starting_date.' Bill Not Created </b>'.$client_details.' <br>'; 
                                    
                                }else{
                                    $anotherday++;
                                    echo $i.'<b style="color:orange"> = '.$recurring_details.' Starting Date -'. $invoice->starting_date.' Another Date </b>'.$client_details.' <br>'; 
                                }
                            }else{
                                $timenotmatch++;
                                //echo $i.'<b style="color:#bf00ff"> = RID - '.$invoice->invoice_id.' Starting Date -'. $invoice->starting_date.' This month condition not match, create next month </b> <br>'; 
                            }
                        }
                    }
                }
            }
        }
        echo '------------------ <br/>';
        echo 'Total m1 Recurring Active - '.$i.'<br/>';
        echo 'Bill Created - '.$created.'<br/>';
        echo 'Bill Not Created - '.$notcreated.'<br/>';
        echo 'Date Match - '.$sameday.'<br/>';
        echo 'Another Date - '.$anotherday.'<br/>';
        echo 'This month condition not match - '.$timenotmatch.'<br/>';
        die;
	    $data['selected_date'] = $request->selected_date;
	    return view('recurring_form', $data);
	}
	
	public function recurring_invoice_create_cron_custom_process(Request $request){
	    
        date_default_timezone_set('Asia/Dhaka');

        // Selected date from user input or system (you can replace this with dynamic date)
        $selectedDate = Carbon::parse($request->selected_date); // Format: j/n/Y
        $selectedDay = date('j', strtotime(str_replace('/', '-', $selectedDate)));
        $selectedMonth = date('n', strtotime(str_replace('/', '-', $selectedDate)));
        $selectedYear = date('Y', strtotime(str_replace('/', '-', $selectedDate)));
        $totalDaysOfTheMonth = cal_days_in_month(CAL_GREGORIAN, $selectedMonth, $selectedYear);
        
        // Prepare dayArray
        $dayArray = [];
        $lastDateOfTheMonth = 0;
        if (intval($selectedDay) == intval($totalDaysOfTheMonth)) {
            $lastDateOfTheMonth = 1;
            $highestDate = 31;
            for ($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++) {
                $dayArray[] = $i;
            }
        } else {
            $dayArray[] = $selectedDay;
        }
        
        // Fetch recurring invoices (monthly)
        $monthlyRecurring = DB::table('invoice_recurring')
            ->where('frequency', 'm1')
            ->where('status', 1)
            ->where(function ($query) use ($selectedMonth, $selectedYear) {
                $query->where(function ($q) use ($selectedMonth, $selectedYear) {
                    $q->whereNotNull('last_invoice_id')
                      ->where(function ($q2) use ($selectedMonth, $selectedYear) {
                          $q2->whereMonth('last_invoice_created_date', '!=', $selectedMonth)
                             ->orWhereYear('last_invoice_created_date', '!=', $selectedYear);
                      });
                })
                ->orWhere(function ($q) {
                    $q->whereNull('last_invoice_id')
                      ->whereNull('last_invoice_created_date');
                });
            })
            ->get();
        
        $generatedCount = 0;
        $skippedCount = 0;
        
        if ($monthlyRecurring) {
            $i = 0;
            foreach ($monthlyRecurring as $invoice) {
                $shouldGenerate = false;
        
                if (!empty($invoice->starting_date)) {
                    $startingDateTimestamp = strtotime($invoice->starting_date);
                    $selectedDateTimestamp = strtotime(str_replace('/', '-', $selectedDate));
        
                    if ($startingDateTimestamp <= $selectedDateTimestamp) {
                        // Check if invoice count limit is reached
                        if ($invoice->number_of_invoice == 0 || $invoice->number_of_created_invoice < $invoice->number_of_invoice) {
                            $startingDay = date('j', strtotime($invoice->starting_date));
        
                            if (in_array($startingDay, $dayArray)) {
                                $shouldGenerate = true;
                            }
                        }
                    }
                }
        
                if ($shouldGenerate) {
                    $i++;
                    // Generate invoice
                    //$this->auto_create_invoice($invoice->invoice_id);
                    $generatedCount++;
        
                    echo $i." - Need Invoice Create: #{$invoice->invoice_id}<br/>";
                } else {
                    $skippedCount++;
                }
            }
        }
        
        // Summary Report
        echo "<hr/>";
        echo "<strong>Monthly Recurring Invoice Report for {$selectedDate}</strong><br/>";
        echo "Total Recurring Invoices: " . count($monthlyRecurring) . "<br/>";
        echo "Invoices Need Created: {$generatedCount}<br/>";
        //echo "Invoices Skipped: {$skippedCount}<br/>";

	    //$data['selected_date'] = $request->selected_date;
	    //return view('recurring_form', $data);
	}
	
	public function recurring_check_invoice_created(){
	    date_default_timezone_set('Asia/Dhaka');
        $start = new \DateTime('2025-08-01');
        $end = new \DateTime('2025-08-31');
        
        // Add one day to $end because DatePeriod excludes the end date
        $end->modify('+1 day');
        
        $interval = new \DateInterval('P1D');
        $period = new \DatePeriod($start, $interval, $end);
        
        $thisMonthDays = [];
        foreach ($period as $date) {
            $thisMonthDays[] = $date->format('d/m/Y');
        }
        $i = 0;
        foreach($thisMonthDays as $day){
            $todayDate = $day;
            $todayDay = date('j',strtotime($day));
            $todayMonth = date('n',strtotime($day));
            $todayYear = date('Y',strtotime($day));
            $totalDaysOfTheMonth =  cal_days_in_month(CAL_GREGORIAN, $todayMonth, $todayYear);
            
            $dayArray = array();
            $lastDateOfTheMonth = 0;
            if(intval($todayDay) == intval($totalDaysOfTheMonth)){
                $lastDateOfTheMonth = 1;
                $highestDate = 31;
                for($i = intval($totalDaysOfTheMonth); $i <= intval($highestDate); $i++){
                    $dayArray[] = $i;
                }
            }else{
                $dayArray[] = $todayDay;
            }
            
            $monthlyRecurring = DB::table('invoice_recurring')->select('invoice_id','client_id','starting_date','invoice_created_date','number_of_invoice','number_of_created_invoice','last_invoice_created_date','last_invoice_id','frequency')->where('status',1)->get();
            
            if($monthlyRecurring){
                foreach($monthlyRecurring as $invoice){
                    if(!empty($invoice->starting_date)){
                        if($invoice->number_of_invoice != 0 && ($invoice->number_of_invoice <= $invoice->number_of_created_invoice)){
                        }else{
                            $startingDate_time = date('U', strtotime($invoice->starting_date));
                            $myDateTime = DateTime::createFromFormat('j/n/Y', $todayDate);
                            $TodayDateString = $myDateTime->format('U');
                            if($startingDate_time <= $TodayDateString){
                                $startingDate = date('j/n/Y', strtotime($invoice->starting_date));
                                $startingDay = date('j', strtotime($invoice->starting_date));
        
                                if(in_array($startingDay, $dayArray)){
                                    
                                    $current_month = date('m');
            						$current_year = date('Y');
            						$check_bill = DB::select("SELECT invoice_id,client_id,invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id'");
            						if($check_bill)
            						{
            						    //$i++;
            						    //echo $i.' ='.$invoice->starting_date.'= 1 months - '.$invoice->invoice_id.' Created<br>';
                                        //$month1++;
            						}else{
            						   $i++;
            						   $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($invoice->client_id);
                                        $client_details = '<a href="' . $clientUrl . '" target="_blank">ILC-' . $invoice->client_id . '</a>';
                                        
                                        $recurringUrl = 'https://admin.infolinkbd.com/recurring/details/' . base64_encode($invoice->invoice_id);
                                        $recurring_details = '<a href="' . $recurringUrl . '" target="_blank">RID-' . $invoice->invoice_id . '</a>';
                                        
                                        echo $i . ') ' . $client_details . ' = ' . $invoice->starting_date . ' = Frequency - ' . $invoice->frequency . ' - Recurring ID - ' . $recurring_details . ' = This month Invoice not created <br>';

            						}
                                }
                            }
                        }
                    }
                }
            }
        }
	}
	
	public function recurring_duplicate_batch($offset = 0, $limit = 500)
    {
        ini_set('max_execution_time', 0);
        set_time_limit(0);
        ini_set('memory_limit','-1');
    
        date_default_timezone_set('Asia/Dhaka');
    
        echo "<h3>Scanning Recurring Invoices: Offset $offset to ".($offset+$limit)."</h3><hr>";
    
        $recurrings = DB::table('invoice_recurring')
            ->where('status',1)
            ->orderBy('invoice_id')
            ->offset($offset)
            ->limit($limit)
            ->get();
    
        $current_month = date('m');
        $current_year = date('Y');
    
        foreach($recurrings as $invoice){
    
            $check_bill = DB::table('invoice')
                ->whereMonth('invoice_created_date',$current_month)
                ->whereYear('invoice_created_date',$current_year)
                ->where('client_id',$invoice->client_id)
                ->where('invoice_recurring_id',$invoice->invoice_id)
                ->where('status',1)
                ->orderBy('invoice_id')
                ->get();
    
            if($check_bill->count() > 1){
    
                echo "<b style='color:red'>Client ILC-{$invoice->client_id} | RID-{$invoice->invoice_id}</b><br>";
    
                foreach($check_bill as $row){
                    $url = url('invoice/details/'.base64_encode($row->invoice_id));
                    echo "<a target='_blank' href='$url'>IL-{$row->invoice_id}</a><br>";
                }
                echo "-----------------------------<br>";
                
            }
        }
    
        echo "<br><b>Batch Done</b>";
    }
    
    public function recurring_check_invoice_duplicate2()
    {
        ini_set('max_execution_time', 600);
        set_time_limit(600);
        date_default_timezone_set('Asia/Dhaka');
    
        $start = date('Y-m-01 00:00:00');
        $end   = date('Y-m-t 23:59:59');
    
        $duplicates = DB::select("
            SELECT 
                r.invoice_id AS recurring_id,
                r.client_id,
                COUNT(i.invoice_id) AS total_invoice
            FROM invoice_recurring r
            JOIN invoice i 
                ON i.invoice_recurring_id = r.invoice_id
                AND i.client_id = r.client_id
                AND i.status = 1
                AND i.invoice_created_date BETWEEN ? AND ?
            WHERE r.status = 1
              AND r.frequency = 'm1'
            GROUP BY r.invoice_id, r.client_id
            HAVING COUNT(i.invoice_id) > 1
        ", [$start, $end]);
    
        $j = 0;
    
        foreach($duplicates as $dup){
            $j++;
    
            $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($dup->client_id);
            echo $j.") <a href='{$clientUrl}' target='_blank'>ILC-{$dup->client_id}</a> = Duplicate Invoice found RID- {$dup->recurring_id}<br>";
            echo "------------------------------<br>";
    
            $rows = DB::select("
                SELECT invoice_id,status,reason_cancel
                FROM invoice
                WHERE invoice_recurring_id = ?
                  AND client_id = ?
                  AND invoice_created_date BETWEEN ? AND ?
                ORDER BY invoice_id ASC
            ", [$dup->recurring_id,$dup->client_id,$start,$end]);
    
            $main_invoice_id = $rows[0]->invoice_id;
    
            foreach($rows as $key=>$row){
                $statusMap = [
                    1 => "<span style='color:red;'>Due</span>",
                    3 => "<span style='color:green;'>Paid</span>",
                    2 => "<span style='color:blue;'>Partialy Paid</span>",
                    0 => "<span style='color:blue;'>Pending</span>",
                    4 => "Cancel",
                    5 => "Deleted",
                    6 => "Bad Debt",
                ];
                $status = $statusMap[$row->status] ?? '';
    
                $invoiceUrl = 'https://admin.infolinkbd.com/invoice/details/' . base64_encode($row->invoice_id);
                echo "<a href='{$invoiceUrl}' target='_blank'>IL-{$row->invoice_id} - {$status}</a> {$row->reason_cancel}<br>";
    
                // Auto cancel duplicates (keep first invoice)
                if($key > 0){
                    //echo '<b style="color:red">'.$row->invoice_id.' Mark for cancel </b>'.$main_invoice_id.'<br>';
                //     $update = DB::table('invoice')->where('invoice_id',$row->invoice_id)->update([
                //         'status'=>4,
                //         'reason_cancel'=>"Cancelled for duplication. Main invoice: IL-".$main_invoice_id,
                //         'remarks'=>''
                //     ]);
                    
                //     if($update){
	               //     echo $row->invoice_id.' - Updated successfuly <br/>';
	               // }
                }
            }
            echo "--------------------------- <br>";
        }
    }
    
    public function recurring_check_invoice_duplicate()
    {
        $date = '2026-01-01';
    
        $dueList = DB::table('invoice')
                    ->select('client_id', DB::raw('COUNT(*) as total_invoices'))
                    ->where('status', 1)
                    ->whereDate('invoice_created_date', $date)
                    ->groupBy('client_id')
                    ->having('total_invoices', '>', 1)
                    ->orderByDesc('total_invoices')
                    ->get();
        echo '<pre>';
        print_r($dueList);die;
        foreach($dueList as $client){
            
            $monthlyRecurring = DB::table('invoice_recurring')->select('invoice_id','client_id','starting_date','invoice_created_date','number_of_invoice','number_of_created_invoice','last_invoice_created_date','last_invoice_id','frequency')->where('status',1)->where('client_id', $client->client_id)->get();
        
            if($monthlyRecurring){
                
                $j = 0;
                foreach($monthlyRecurring as $invoice){
                    $current_month = date('m');
    				$current_year = date('Y');
    				$check_bill = DB::select("SELECT invoice_id,client_id,status, reason_cancel, invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id' AND status = 1 AND invoice_recurring_id = '$invoice->invoice_id'");
    				
    				if (count($check_bill) > 1) {
    				    $j++;
    				   $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($invoice->client_id);
                        $client_details = '<a href="' . $clientUrl . '" target="_blank">ILC-' . $invoice->client_id . '</a>';
                        
                        echo $j . ') ' . $client_details . ' = Duplicate Invoice found RID- '. $invoice->invoice_id .'<br>';
                        echo '------------------------------<br/>';
                        foreach($check_bill as $key => $row){
                            if($row->status==1)
    						{
    							$status = "<span style='color:red;'>Due</span>";
    						}elseif($row->status==3){
    							$status = "<span style='color:green;'>Paid</span>";
    						}elseif($row->status==2){
    							$status = "<span style='color:blue;'>Partialy Paid</span>";
    						}elseif($row->status==0){
    							$status = "<span style='color:blue;'>Pending</span>";
    						}elseif($row->status==4){
    							$status = "Cancel";
    						}elseif($row->status==5){
    							$status = "Deleted";
    						}elseif($row->status==6){
    							$status = "Bad Debt";
    						}else{
    							$status = '';
    						}
    						$invoiceUrl = 'https://admin.infolinkbd.com/invoice/details/' . base64_encode($row->invoice_id);
                            $invoice_details = '<a href="' . $invoiceUrl . '" target="_blank">' . 'IL-'.$row->invoice_id.' - '.$status .'</a> '.$row->reason_cancel;
        		            echo $invoice_details.'<br/>';
        		            if($key == 0){
        		                $main_invoice_id = $row->invoice_id;
        		            }
        		            if($key == 1){
        		                //echo '<b style="color:red">'.$row->invoice_id.' Mark for cancel </b>'.$main_invoice_id.'<br>';
        		              //  $update = DB::table('invoice')->where('invoice_id',$row->invoice_id)->update(array('status'=> 4,'reason_cancel' => "Cancelled for duplication. Duplicate invoice ID: IL-$main_invoice_id",'remarks'=>''));
        		              //  if($update){
        		              //      echo $row->invoice_id.' - Updated successfuly <br/>';
        		              //  }
        		                
        		            }
                        }
                        echo '--------------------------- <br/>';
    				}
                }
            }
            
        }
    }
	
	public function recurring_check_invoice_duplicate3(){
	    
	    ini_set('max_execution_time', 600); // 10 minutes
        set_time_limit(600);

	    date_default_timezone_set('Asia/Dhaka');

        $monthlyRecurring = DB::table('invoice_recurring')->select('invoice_id','client_id','starting_date','invoice_created_date','number_of_invoice','number_of_created_invoice','last_invoice_created_date','last_invoice_id','frequency')->where('status',1)->where('frequency', 'm1')->get();
        
        if($monthlyRecurring){
            
            $j = 0;
            foreach($monthlyRecurring as $invoice){
                $current_month = date('m');
				$current_year = date('Y');
				$check_bill = DB::select("SELECT invoice_id,client_id,status, reason_cancel, invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id' AND status = 1 AND invoice_recurring_id = '$invoice->invoice_id'");
				if (count($check_bill) > 1) {
				    $j++;
				   $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($invoice->client_id);
                    $client_details = '<a href="' . $clientUrl . '" target="_blank">ILC-' . $invoice->client_id . '</a>';
                    
                    echo $j . ') ' . $client_details . ' = Duplicate Invoice found RID- '. $invoice->invoice_id .'<br>';
                    echo '------------------------------<br/>';
                    foreach($check_bill as $key => $row){
                        if($row->status==1)
						{
							$status = "<span style='color:red;'>Due</span>";
						}elseif($row->status==3){
							$status = "<span style='color:green;'>Paid</span>";
						}elseif($row->status==2){
							$status = "<span style='color:blue;'>Partialy Paid</span>";
						}elseif($row->status==0){
							$status = "<span style='color:blue;'>Pending</span>";
						}elseif($row->status==4){
							$status = "Cancel";
						}elseif($row->status==5){
							$status = "Deleted";
						}elseif($row->status==6){
							$status = "Bad Debt";
						}else{
							$status = '';
						}
						$invoiceUrl = 'https://admin.infolinkbd.com/invoice/details/' . base64_encode($row->invoice_id);
                        $invoice_details = '<a href="' . $invoiceUrl . '" target="_blank">' . 'IL-'.$row->invoice_id.' - '.$status .'</a> '.$row->reason_cancel;
    		            echo $invoice_details.'<br/>';
    		            if($key == 0){
    		                $main_invoice_id = $row->invoice_id;
    		            }
    		            if($key == 1){
    		                //echo '<b style="color:red">'.$row->invoice_id.' Mark for cancel </b>'.$main_invoice_id.'<br>';
    		              //  $update = DB::table('invoice')->where('invoice_id',$row->invoice_id)->update(array('status'=> 4,'reason_cancel' => "Cancelled for duplication. Duplicate invoice ID: IL-$main_invoice_id",'remarks'=>''));
    		              //  if($update){
    		              //      echo $row->invoice_id.' - Updated successfuly <br/>';
    		              //  }
    		                
    		            }
                    }
                    echo '--------------------------- <br/>';
				}
            }
        }
        
	}
	
	public function recurring_check_invoice_duplicate_other(){
	    date_default_timezone_set('Asia/Dhaka');

        $monthlyRecurring = DB::table('invoice_recurring')->select('invoice_id','client_id','starting_date','invoice_created_date','number_of_invoice','number_of_created_invoice','last_invoice_created_date','last_invoice_id','frequency')->where('status',1)->get();
        
        if($monthlyRecurring){
            $j = 0;
            foreach($monthlyRecurring as $invoice){
                $current_month = date('m');
				$current_year = date('Y');
				$check_bill = DB::select("SELECT invoice_id,client_id,status, reason_cancel,invoice_recurring_id,invoice_created_date FROM invoice WHERE MONTH(invoice_created_date) = '$current_month' AND YEAR(invoice_created_date) = '$current_year' AND client_id = '$invoice->client_id'");
				if (count($check_bill) > 1) {
				    $j++;
				   $clientUrl = 'https://admin.infolinkbd.com/client/details/' . base64_encode($invoice->client_id);
                    $client_details = '<a href="' . $clientUrl . '" target="_blank">ILC-' . $invoice->client_id . '</a>';
                    
                    echo $j . ') ' . $client_details . ' = Duplicate Invoice found RID- '. $invoice->invoice_id .'<br>';
                    echo '------------------------------<br/>';
                    foreach($check_bill as $key => $row){
                        if($row->status==1)
						{
							$status = "<span style='color:red;'>Due</span>";
						}elseif($row->status==3){
							$status = "<span style='color:green;'>Paid</span>";
						}elseif($row->status==2){
							$status = "<span style='color:blue;'>Partialy Paid</span>";
						}elseif($row->status==0){
							$status = "<span style='color:blue;'>Pending</span>";
						}elseif($row->status==4){
							$status = "Cancel";
						}elseif($row->status==5){
							$status = "Deleted";
						}elseif($row->status==6){
							$status = "Bad Debt";
						}else{
							$status = '';
						}
						$invoiceUrl = 'https://admin.infolinkbd.com/invoice/details/' . base64_encode($row->invoice_id);
                        $invoice_details = '<a href="' . $invoiceUrl . '" target="_blank">' . 'IL-'.$row->invoice_id.' - '.$status .'</a> RID-'.$row->invoice_recurring_id;
    		            echo $invoice_details.'<br/>';
    		            
                    }
                    echo '--------------------------- <br/>';
				}
            }
        }
        
	}
	
	public function recurring_check_invoice_id(){
	    
        $currentMonth = date('m'); 
        $currentYear = date('Y'); 
        
        $monthlyRecurring = DB::table('invoice_recurring')
            ->where('frequency', 'm1')
            ->where('status', 1)
            ->where(function ($query) use ($currentMonth, $currentYear) {
                $query->where(function ($q) use ($currentMonth, $currentYear) {
                    // Condition-1: last_invoice_id ache and date current month na
                    $q->whereNotNull('last_invoice_id')
                      ->where(function ($q2) use ($currentMonth, $currentYear) {
                          $q2->whereMonth('last_invoice_created_date', '!=', $currentMonth)
                             ->orWhereYear('last_invoice_created_date', '!=', $currentYear);
                      });
                })
                ->orWhere(function ($q) {
                    // Condition-2: last_invoice_id o last_invoice_created_date duita e null
                    $q->whereNull('last_invoice_id')
                      ->whereNull('last_invoice_created_date');
                });
            })
            ->get();

	    echo '<pre>';
	    print_r($monthlyRecurring);die;
	}
}
