<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Carbon;

class UpdateDLRStatus extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'dlr:update';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Update DLR status from sent_sms table';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $this->info('Starting DLR update process...');
        
        try 
        {
            // Close connections before starting
            $this->closeDatabaseConnections();
            
            // Delete MT records from sent_sms
            $deleted = DB::table('sent_sms')
                ->where('momt', 'MT')
                ->delete();
            
            $this->info("Deleted {$deleted} MT records from sent_sms");
            
            // Process in smaller batches
            $batchSize = 500;
            $totalProcessed = 0;
            $continueProcessing = true;
            
            while($continueProcessing) 
            {
                // Delete orphaned DLR records with no dlr_url - they cannot be matched
                // to any send_numbers row and would loop forever since whereIn(null) never deletes them
                DB::table('sent_sms')
                    ->where('momt', 'DLR')
                    ->whereNull('dlr_url')
                    ->delete();

                // Get DLR records that need to be processed
                $dlrRecords = DB::table('sent_sms')
                            ->where('update_dlr', 0)
                            ->where('momt', 'DLR')
                            ->whereNotNull('dlr_url')
                            ->limit($batchSize)
                            ->lockForUpdate()
                            ->get([
                                'smsc_id',
                                'time',
                                'dlr_url',
                                DB::raw("LOCATE('NACK', msgdata) as msgdata_nack"),
                                'dlr_mask',
                                'msgdata'
                            ]);

                $dlrCount = $dlrRecords->count();
                
                if($dlrCount === 0) 
                {
                    $continueProcessing = false;
                    break;
                }
                
                $this->info("Processing batch of {$dlrCount} DLR records...");
                
                $masterIds = [];
                $processedInBatch = 0;

                foreach($dlrRecords as $dlrRow) 
                {
                    $msgdataNack = $dlrRow->msgdata_nack;
                    $deliveredTime = $dlrRow->time;
                    $dlrUrl = $dlrRow->dlr_url;
                    $dlrMask = $dlrRow->dlr_mask;
                    $msgdata = $dlrRow->msgdata;
                    $smscName = $dlrRow->smsc_id;

                    // Extract error code from msgdata
                    $errCode = '045'; // default
                    
                    if(preg_match('/err%3A(\d+)/', $msgdata, $matches)) 
                    {
                        $errCode = $matches[1];
                    }

                    if($msgdataNack > 0)
                    {
                        // NACK found — log full SMSC response before deletion
                        Log::error('DLR NACK received', [
                            'dlr_url'         => $dlrUrl,
                            'smsc_id'         => $smscName,
                            'dlr_mask'        => $dlrMask,
                            'err_code_parsed' => $errCode,
                            'msgdata_raw'     => $msgdata,
                        ]);
                        $newStatus  = 'Failed';
                        $getErrCode = '045';
                    }
                    elseif($dlrMask == 1)
                    {
                        $newStatus  = 'Delivered';
                        $getErrCode = '000';
                    }
                    else
                    {
                        $getErrCode  = $errCode;

                        // Try exact match first
                        $errorRecord = DB::table('error_code')
                                        ->where('error_code', $getErrCode)
                                        ->where('isActive', 1)
                                        ->first(['error_status']);

                        // Fallback: try zero-padded 3-digit code (e.g. '74' → '074')
                        if (!$errorRecord && strlen($getErrCode) < 3) {
                            $paddedCode  = str_pad($getErrCode, 3, '0', STR_PAD_LEFT);
                            $errorRecord = DB::table('error_code')
                                            ->where('error_code', $paddedCode)
                                            ->where('isActive', 1)
                                            ->first(['error_status']);
                        }

                        $newStatus = $errorRecord ? $errorRecord->error_status : 'Failed';
                    }

                    $dlrUpdateData = [
                        'status'         => $newStatus,
                        'err_code'       => $getErrCode,
                        'delivered_date' => $deliveredTime,
                    ];
                    
                    //   For old records without prefix, check send_numbers first, fallback to schedule_send_numbers
                    if (is_string($dlrUrl) && str_starts_with($dlrUrl, 's'))
                    {
                        // New format: explicitly marked as scheduled
                        $actualId = substr($dlrUrl, 1);
                        DB::table('schedule_send_numbers')
                            ->where('id', $actualId)
                            ->update($dlrUpdateData);
                    }
                    else
                    {
                        // Numeric dlr_url: try send_numbers first (immediate SMS)
                        $updated = DB::table('send_numbers')
                            ->where('id', $dlrUrl)
                            ->where('cut_off', 0)
                            ->update($dlrUpdateData);

                        // Fallback: if not found in send_numbers, check schedule_send_numbers
                        // (handles old DLRs created before the "s" prefix was added)
                        if ($updated === 0)
                        {
                            DB::table('schedule_send_numbers')
                                ->where('id', $dlrUrl)
                                ->update($dlrUpdateData);
                        }
                    }

                    $masterIds[] = $dlrUrl;
                    
                    $processedInBatch++;
                    
                    // Close connections every 100 records in batch
                    if($processedInBatch % 100 === 0) 
                    {
                        $this->closeDatabaseConnections();
                    }
                }

                // Delete processed DLR records from sent_sms
                if(!empty($masterIds)) 
                {
                    // Use chunking for large deletions
                    $chunks = array_chunk($masterIds, 100);
                    
                    foreach($chunks as $chunk) 
                    {
                        DB::table('sent_sms')
                            ->whereIn('dlr_url', $chunk)
                            ->delete();
                    }
                    
                    $totalProcessed += count($masterIds);
                    $this->info("Processed and deleted " . count($masterIds) . " DLR records in this batch");
                }
                
                // Close connections after each batch
                $this->closeDatabaseConnections();
                
                // Small sleep to prevent overwhelming the database
                usleep(100000); // 0.1 second
                
                // Break if we've processed enough
                if($dlrCount < $batchSize) 
                {
                    $continueProcessing = false;
                }
            }

            $this->info("DLR update process completed successfully. Total processed: {$totalProcessed} records");
            
        } 
        catch(\Exception $e) 
        {
            Log::error('DLR Update Error: ' . $e->getMessage());
            $this->error('Error occurred: ' . $e->getMessage());
        }
        finally 
        {
            // Always close connections
            $this->closeDatabaseConnections();
            gc_collect_cycles();
        }
    }
    
    /**
     * Close database connections
     */
    private function closeDatabaseConnections()
    {
        try 
        {
            DB::disconnect();
            
            // Clear connection pool
            $connections = ['mysql'];
            
            foreach($connections as $connection)
            {
                try 
                {
                    $manager = DB::connection($connection);
                    
                    if(method_exists($manager, 'getPdo')) 
                    {
                        $pdo = $manager->getPdo();
                        if ($pdo) {
                            $pdo = null;
                        }
                    }
                } 
                catch(\Exception $e) 
                {
                    // Ignore errors when closing connections
                }
            }
            
            // Clear any cached connections
            if(app()->bound('db')) 
            {
                $databaseManager = app('db');
                foreach (config('database.connections') as $name => $config) 
                {
                    try 
                    {
                        $databaseManager->purge($name);
                    } 
                    catch(\Exception $e) 
                    {
                        // Ignore purge errors
                    }
                }
            }
        } 
        catch(\Exception $e) 
        {
            // Silently fail on connection cleanup
        }
    }
}