<?php

namespace App\Console\Commands;

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

class NotificationCheckManageNDT extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'notifications:check-manage-ndt-unnecessary';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Check for unnecessary manual NDT requests where NDE Matrix has 0% ratio';

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $startTime = microtime(true);
        $commandName = $this->signature;
        
        // Log command start
        logNotificationStart($commandName);
        
        $this->info('Checking for unnecessary manual NDT requests...');
        
        $totalCount = 0;
        $totalSent = 0;
        $totalRecords = 0;
        $totalNew = 0;
        
        try {
            // Check each NDT type
            $result1 = $this->checkTestType('RT', 'rt', 'rt_request_no');
            $totalCount += $result1;
            $result2 = $this->checkTestType('UT', 'ut', 'ut_request_no');
            $totalCount += $result2;
            $result3 = $this->checkTestType('PT', 'pt', 'pt_request_no');
            $totalCount += $result3;
            $result4 = $this->checkTestType('MT', 'mt', 'mt_request_no');
            $totalCount += $result4;
            
            $duration = microtime(true) - $startTime;
            logNotificationComplete($commandName, $totalSent, $totalRecords, $totalNew, $duration);
            
            $this->info("Total notifications sent: {$totalCount}");
            return 0;
        
        } catch (\Exception $e) {
            $duration = microtime(true) - $startTime;
            logNotificationError($commandName, $e->getMessage(), [
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'duration' => $duration,
            ]);
            
            $this->error("Error: " . $e->getMessage());
            return 1;
        }
    }
    
    /**
     * Check for unnecessary requests for a specific test type
     * 
     * @param string $testName Display name (RT, UT, PT, MT)
     * @param string $ndeField NDE Matrix field name (rt, ut, pt, mt)
     * @param string $requestField Weldlog request field name (rt_request_no, ut_request_no, etc.)
     * @return int
     */
    private function checkTestType($testName, $ndeField, $requestField)
    {
        $this->info("Checking {$testName} unnecessary requests...");
        
        $ids = apply_welded_filter(DB::table('weld_logs'))
            ->leftJoin('nde_matrices', function($join) {
                $join->on('weld_logs.line_number', '=', 'nde_matrices.line')
                     ->on('weld_logs.type_of_joint', '=', 'nde_matrices.type_of_joint');
            })
            ->whereNotNull('weld_logs.welding_date')
            ->where('weld_logs.welding_date', '!=', '')
            ->whereNotNull("weld_logs.{$requestField}")
            ->where("weld_logs.{$requestField}", '!=', '')
            ->where(function($query) use ($ndeField) {
                $query->whereNull("nde_matrices.{$ndeField}")
                      ->orWhere("nde_matrices.{$ndeField}", 0)
                      ->orWhere("nde_matrices.{$ndeField}", '0');
            })
            ->pluck('weld_logs.id')
            ->toArray();
        
        if (empty($ids)) {
            $this->info("No unnecessary {$testName} requests found.");
            return 0;
        }
        
        $notificationCode = 'notification_manage_ndt_unnecessary';
        
        // Skip if checked recently
        if (!shouldCheckNotification($notificationCode)) {
            $this->info("Skipping {$testName}: Last notification sent less than 5 minutes ago.");
            return 0;
        }
        
        $lastCheck = getLastCheckTimestamp($notificationCode);
        
        // Filter IDs: only keep new ones if not first run
        if ($lastCheck !== null) {
            $alreadyNotifiedIds = getAlreadyNotifiedIds($notificationCode);
            if (!empty($alreadyNotifiedIds)) {
                $newIds = array_diff($ids, $alreadyNotifiedIds);
                if (empty($newIds)) {
                    $this->info("Skipping {$testName}: No new records since last notification.");
                    return 0;
                }
                $ids = array_values(array_unique(array_merge($alreadyNotifiedIds, $ids)));
            }
        }
        
        $filterParams = [
            'table' => 'weld_logs',
            'conditions' => [
                'id' => array_values(array_unique($ids))
            ]
        ];
        
        if ($lastCheck !== null && isNotificationDuplicate($notificationCode, $filterParams)) {
            $this->info("Skipping {$testName}: Same issue already notified and no new records.");
            return 0;
        }
        
        $newCount = $lastCheck !== null ? count(array_diff($ids, getAlreadyNotifiedIds($notificationCode))) : count($ids);
        $sentCount = sendBatchNotificationWithFilter(
            $notificationCode,
            "Unnecessary {$testName} Request",
            '%d joint(s) have unnecessary ' . $testName . ' test requests (NDE ratio 0% or missing).',
            $filterParams,
            count($filterParams['conditions']['id'])
        );
        
        $this->info("Sent batch notification for unnecessary {$testName} requests (" . count($filterParams['conditions']['id']) . " joints, {$newCount} new).");
        return $sentCount > 0 ? 1 : 0;
    }
}