<?php 
//document_revisions
$table = "naks_certificates";
$mainPath = "003_Welding_Database/0000_Naks Technology";
$nullDownload = db($table);
if(isset($fileName)) {
    // Parse fileName to extract short_number and certificate_no
    $fileNameParts = explode('-', $fileName);
    if(count($fileNameParts) >= 2) {
        $shortNumber = $fileNameParts[0]; // АЦСТ-161 or АЦСТ-11
        
        // Handle different certificate number formats
        if(count($fileNameParts) >= 3) {
            // Format: АЦСТ-11-08091_ОХНВП(16)_РАД.pdf
            // Combine the remaining parts and extract the numeric part
            $remainingParts = implode('-', array_slice($fileNameParts, 2));
            $certificateNo = $fileNameParts[1]; // 08091
            
            // Extract numeric part from the remaining string (before underscore)
            if(preg_match('/^(\d+)/', $remainingParts, $matches)) {
                $certificateNo = $matches[1];
            }
        } else {
            // Original format: АЦСТ-161-00050.pdf
            $certificateNo = $fileNameParts[1]; // 00050
        }
        
        // Remove file extension if present
        $certificateNo = str_replace('.pdf', '', $certificateNo);
        dump($shortNumber, $certificateNo);
        // Update the query to search by both short_number and certificate_no
        $nullDownload = $nullDownload->where("short_number", "like", "%$shortNumber%")
                                    ->where("certificate_no", "like", "%$certificateNo%");
    }
}
$nullDownload = $nullDownload->get();

DB::beginTransaction();

$say = 0;
$k = 0;
Log::debug("📄$table PDF Link to DB Start " . simdi());
try {
    foreach($nullDownload AS $ne) {
        $searchTerm = $ne->short_number;
        $certNo = isset($ne->certificate_no) ? $ne->certificate_no : '';
        
        // First try: exact match with both short_number and certificate_no
        if(trim($searchTerm) != "" && trim($certNo) != "") {
            // Try different zero-prefix patterns for certificate_no
            $foundFile = false;
            
            // Original certificate number from database
            $certPatterns = [$certNo];
            
            // Clean leading zeros and generate additional search patterns
            $trimmedCertNo = ltrim($certNo, '0');
            if($trimmedCertNo != $certNo) {
                // Add trimmed version (no leading zeros)
                $certPatterns[] = $trimmedCertNo;
                
                // Add versions with different numbers of leading zeros
                for($i = 1; $i <= 4; $i++) {
                    $certPatterns[] = str_pad($trimmedCertNo, $i, '0', STR_PAD_LEFT);
                }
            }
            
            $updateData = ['download' => null];
            
            // Try each pattern until we find a match
            foreach($certPatterns as $certPattern) {
                // Format 1: short_number-certPattern (primary format)
                $searchData = "*{$searchTerm}-{$certPattern}*";
                $searchData = str_replace("/", "*", $searchData);
                $searchData = str_replace(" ", "*", $searchData);
                
                $path = "storage/documents/$mainPath/{$searchData}";
                $searchPDF = glob($path);
                
                if(isset($searchPDF[0])) {
                    $updateData['download'] = $searchPDF[0];
                    $foundFile = true;
                    break;
                }
            }
            
            // If no match found with primary format, try just the short_number as fallback
            if(!$foundFile) {
                $data = "*$searchTerm";
                $data = str_replace("/", "*", $data);
                $data = str_replace(" ", "*", $data);
                
                $path = "storage/documents/$mainPath/{$data}*";
                $searchPDF = glob($path);
                
                if(isset($searchPDF[0])) {
                    $updateData['download'] = $searchPDF[0];
                }
            }
          
            db($table)
                ->where("id", $ne->id)
                ->update($updateData);
                $k++;
                
            if($say == $commitSize) {
                DB::commit();
                DB::beginTransaction();
                $say = 0;
            }
    
            $say++;
        }
    }
    DB::commit();
    Log::debug("$table update PDF link for $k rows");
    //dump("$table update PDF link for $k rows");

} catch (\Throwable $th) {
    DB::rollback();
    Log::error("not update PDF links " . $th->getMessage());
    ////dump($th->getMessage());
}

Log::debug("📄$table PDF Link to DB Finish " . simdi());

?>