<?php 
use App\Models\WeldLog; 

$lastIdPrefix = "spool-status-changer";
$lastId = 0;

if(Cache::has($lastIdPrefix)) {
    $lastId = Cache::get($lastIdPrefix);
}
//echo $lastId;

use Carbon\Carbon;
//dump("$lastIdPrefix $lastId");
Log::debug("$lastIdPrefix start " . simdi());

// Initializing filter variables at the top to avoid undefined variable errors in scoped updates
$filterSpoolNumber = null;
if(!getesit("spool_number", "")) {
    $filterSpoolNumber = get("spool_number");
} else if(isset($spool_number)) {
    $filterSpoolNumber = $spool_number;
}

$filterIsoNumber = null;
if(!getesit("iso_number", "")) {
    $filterIsoNumber = get("iso_number");
} else if(isset($iso_number)) {
    $filterIsoNumber = $iso_number;
}

$filterPaintChanged = false;
if(isset($paintChanged) && $paintChanged) {
    $filterPaintChanged = true;
} else if(getesit("paintChanged", "true")) {
    $filterPaintChanged = true;
}

$updateEmptyQuery = db("weld_logs")->whereRaw("NULLIF(spool_status, '') IS NULL");

if($filterIsoNumber) {
    $updateEmptyQuery->where("iso_number", $filterIsoNumber);
}
if($filterSpoolNumber) {
    $updateEmptyQuery->where("spool_number", $filterSpoolNumber);
}

$updateEmpty = apply_welded_filter($updateEmptyQuery)->update([
        'spool_status' => "Waiting"
    ]);

    
//dump("$updateEmpty spool_status empty updated");


    $spoolLists = db("weld_logs")
        ->select(
            "id", 
            "spool_status", 
            "painting_cycle", 
            "welding_date", 
            "spool_number", 
            "iso_number",
            "updated_at", 
            "type_of_joint",
            "no_of_the_joint_as_per_as_built_survey"
        );

        Log::info("Filter paint changed: {$filterPaintChanged}");
        
        if($filterSpoolNumber) {
            $spoolLists = $spoolLists->where("spool_number", $filterSpoolNumber);
            Log::info("Spool status changer filtering by spool_number: {$filterSpoolNumber}");
        }

        if($filterIsoNumber) {
            $spoolLists = $spoolLists->where("iso_number", $filterIsoNumber);
            Log::info("Spool status changer filtering by iso_number: {$filterIsoNumber}");
        }

        if(!getesit("rollback", "")) {
            $rollbackData = db("weld_logs")
                ->where("id", get("rollback"))
                ->first();

            if($rollbackData) {
                $spoolLists = $spoolLists->where("iso_number", $rollbackData->iso_number)
                ->where("spool_number", $rollbackData->spool_number);
            }
        }
        if($filterPaintChanged) {
           Log::info("Paint changed paint status refactored");
        } else {
            $spoolLists = $spoolLists
                ->whereNotIn("spool_status", ['NDT Release', 'Paint', 'Completed']); 
        }

        $spoolLists = apply_welded_filter($spoolLists);
        
        $spoolLists = $spoolLists
        ->where("no_of_the_joint_as_per_as_built_survey", "not like", "%clone%");

        $spoolLists = $spoolLists
            ->orderBy("updated_at", "DESC");
            
        $spoolLists = $spoolLists
            ->get();


    DB::beginTransaction();
    $k = 0;
    $say = 0;
    try {
        foreach($spoolLists AS $spoolList) {

            $isoNumber = $spoolList->iso_number;
            $spoolNumber = $spoolList->spool_number;

            $otherSpools = apply_welded_filter(
                db("weld_logs")
                    ->where("iso_number", $isoNumber)
                    ->where("spool_number", $spoolNumber)
            )->get();

            $otherSpoolStatus = [];
            $otherSpoolTypeOfJoint = [];
            foreach($otherSpools AS $otherSpool) {
                $otherSpoolStatus[] = $otherSpool->spool_status;
                $otherSpoolTypeOfJoint[] = $otherSpool->type_of_joint;
            }

            $spoolStatus = "Waiting";
            $weldingDateChecks = [];
        // //dump("{$spoolList->iso_number} {$spoolList->spool_number}");
            $allSpoolIsField = true;

            $otherSpoolStatusCompletedCheck = false;
            $otherSpoolShopCheck = false;

            foreach($otherSpools AS $otherSpool) {

                
                if($otherSpool->type_of_joint != "F") {
                    $weldingDateCheck = false;
                    $allSpoolIsField = false;
                    

                    if(in_array("Completed", $otherSpoolStatus)) {
                        $otherSpoolStatusCompletedCheck = true;
                    }
        
                    if(in_array("S", $otherSpoolTypeOfJoint)) {
                        $otherSpoolShopCheck = true;
                    }

                    if(!rejected_date($otherSpool->welding_date)) {
                        $weldingDateCheck = true;
                    }
                    $weldingDateChecks[] = $weldingDateCheck;
                }
                
                
            }


            /*
            if($spoolList->iso_number == "PG-900-AH01PG-FA00.030-HC") {
                if($spoolList->no_of_the_joint_as_per_as_built_survey == "6-NEW") {
                   dump($otherSpoolStatusCompletedCheck);
                   dump($otherSpoolTypeOfJoint);
                   dump($weldingDateChecks);
                   dd($otherSpoolShopCheck);
                }
            }
            */
                

            if(in_array(false, $weldingDateChecks) && in_array(true, $weldingDateChecks)) {
                $spoolStatus = "On Going";
            } else if(in_array(false, $weldingDateChecks) && !in_array(true, $weldingDateChecks)) {
                $spoolStatus = "Waiting";
            } else if(!in_array(false, $weldingDateChecks) && in_array(true, $weldingDateChecks)) {
                
                if($spoolList->type_of_joint == "F") {
                    if($otherSpoolStatusCompletedCheck && $otherSpoolShopCheck) {
                        $spoolStatus = "Completed";
                    } else {
                        $spoolStatus = "Spool Release";
                    }
                } else {
                    if($filterPaintChanged) {
                        $spoolStatus = "Completed";
                    } else {
                        $spoolStatus = "Spool Release";
                    }
                }
                
            }


            if($allSpoolIsField) {
                if($spoolList->painting_cycle != "") {
                    $spoolStatus = "Paint";
                } else {
                    $spoolStatus = "Completed";
                }
                
            }

            
            db("weld_logs")
            /*
                ->where("iso_number", $isoNumber)
                ->where("spool_number", $spoolNumber)
                */
                ->where("id", $spoolList->id)
             //   ->whereNotIn("spool_status", ['NDT Release', 'Paint', 'Completed'])
                ->update([
                    'spool_status' => $spoolStatus,
                   // 'spool_ndt_check' => null,
                   // 'spool_ndt_check_no' => null,

                ]);

            // Update construction_paint_logs table based on iso_drawings and spool
            // Sync spool_status from weld_logs to construction_paint_logs automatically
            
                
                $k++;
                
                /*
if($say == $commitSize) {
                    DB::commit();
                    DB::beginTransaction();
                    $say = 0;
                }
*/
                $say++;
        }
        DB::commit();
    } catch (\Throwable $th) {
        //throw $th;
      //  dump($th);
      Log::error($th->getMessage());
        DB::rollback();
    }

    $updatePaintQuery = db("weld_logs")
        ->whereIn("spool_status", ["Completed", "NDT Release"])
        //painting cycle dolu ise 
        ->where(function($query) {
            $query->whereNotNull("painting_cycle")
                  ->orWhere("painting_cycle", "<>", "");
        })

        //paint release date boş ise 
        ->where(function($query) {
            $query->whereNull("paint_release_date")
                  ->orWhere("paint_release_date", "");
        })

        //ndt release date dolu ise
        ->where(function($query) {
            $query->whereNotNull("ndt_release_date")
                  ->orWhere("ndt_release_date", "<>", "");
        });

    if($filterIsoNumber) {
        $updatePaintQuery->where("iso_number", $filterIsoNumber);
    }

    if($filterSpoolNumber) {
        $updatePaintQuery->where("spool_number", $filterSpoolNumber);
    }

    $updatePaint = $updatePaintQuery->update([
        'spool_status' => "Paint"
    ]);

    $updateCompletedQuery = db("weld_logs")
    //    ->whereIn("spool_status", ["Paint"])
        ->where(function($query) {
            $query->whereNotNull("painting_cycle")
                  ->orWhere("painting_cycle", "<>", "");
        })
        ->where(function($query) {
            $query->whereNotNull("paint_release_date")
                  ->orWhere("paint_release_date", "<>", "");
        })
        //ndt release date dolu ise
        ->where(function($query) {
            $query->whereNotNull("ndt_release_date")
                  ->orWhere("ndt_release_date", "<>", "");
        });

    if($filterIsoNumber) {
        $updateCompletedQuery->where("iso_number", $filterIsoNumber);
    }

    if($filterSpoolNumber) {
        $updateCompletedQuery->where("spool_number", $filterSpoolNumber);
    }

    $updateCompleted = $updateCompletedQuery->update([
        'spool_status' => "Completed"
    ]);

    //dump("$k processed");
    
    // Performance monitoring - Start paint sync timing
    $paintSyncStartTime = microtime(true);
    
    $statusLog = "$lastIdPrefix finish $k processed 
    updateComplete $updateCompleted updatePaint $updatePaint " . simdi();
   // dump("Status Changer Affected Columns $k");
    Log::debug($statusLog . simdi());

    // OPTIMIZED SOLUTION 1: Using JOIN with Window Function (Best Performance)
    $cteConditions = ["spool_status IS NOT NULL"];
    $cteParams = [];
    $whereConditions = [];
    $updateParams = [];
    
    // Check for spool_number parameter (GET or variable)
    if(!getesit("spool_number", "") || isset($spool_number)) {
        $spoolNumberValue = !getesit("spool_number", "") ? get("spool_number") : $spool_number;
        $cteConditions[] = "spool_number = ?";
        $cteParams[] = $spoolNumberValue;
        $whereConditions[] = "cpl.spool = ?";
        $updateParams[] = $spoolNumberValue;
    }
    
    // Check for line_number parameter (GET or variable)  
    if(!getesit("iso_number", "") || isset($iso_number)) {
        $isoNumberValue = !getesit("iso_number", "") ? get("iso_number") : $iso_number;
        $cteConditions[] = "iso_number = ?";
        $cteParams[] = $isoNumberValue;
        $whereConditions[] = "cpl.iso_drawings = ?";
        $updateParams[] = $isoNumberValue;
    }

    $cteWhereClause = "WHERE " . implode(" AND ", $cteConditions);
    
    // Build WHERE clause for main query
    $whereClause = !empty($whereConditions) ? " WHERE " . implode(" AND ", $whereConditions) : "";
    
    // Optimized query using CTE and Window Function (MySQL 8.0+)
    $sqlQuery = "
        WITH latest_weld_logs AS (
            SELECT 
                iso_number,
                spool_number, 
                spool_status,
                ROW_NUMBER() OVER (
                    PARTITION BY iso_number, spool_number 
                    ORDER BY updated_at DESC
                ) as rn
            FROM weld_logs 
            $cteWhereClause
        )
        UPDATE construction_paint_logs cpl 
        INNER JOIN latest_weld_logs lwl ON (
            lwl.iso_number = cpl.iso_drawings 
            AND lwl.spool_number = cpl.spool 
            AND lwl.rn = 1
        )
        SET cpl.spool_status = lwl.spool_status" . $whereClause;
    
    try {
        $allParams = array_merge($cteParams, $updateParams);
        if(!empty($allParams)) {
            DB::statement($sqlQuery, $allParams);
        } else {
            DB::statement($sqlQuery);
        }
        Log::info("Paint logs sync completed successfully using optimized CTE query");
    } catch (\Exception $e) {
        Log::warning("CTE query failed, falling back to batch processing: " . $e->getMessage());
        
        // FALLBACK SOLUTION: Batch Processing for MySQL < 8.0 or large datasets
        $batchSize = 1000;
        $offset = 0;
        $totalUpdated = 0;
        
        do {
            // Get latest spool status for each iso_number + spool_number combination
            $latestStatuses = DB::table('weld_logs as wl1')
                ->select('wl1.iso_number', 'wl1.spool_number', 'wl1.spool_status')
                ->leftJoin('weld_logs as wl2', function($join) {
                    $join->on('wl1.iso_number', '=', 'wl2.iso_number')
                         ->on('wl1.spool_number', '=', 'wl2.spool_number')
                         ->whereRaw('wl1.updated_at < wl2.updated_at');
                })
                ->whereNull('wl2.id')
                ->whereNotNull('wl1.spool_status')
                ->when($filterIsoNumber, function($query) use ($filterIsoNumber) {
                    return $query->where('wl1.iso_number', $filterIsoNumber);
                })
                ->when($filterSpoolNumber, function($query) use ($filterSpoolNumber) {
                    return $query->where('wl1.spool_number', $filterSpoolNumber);
                })
                ->limit($batchSize)
                ->offset($offset)
                ->get();
                
            if($latestStatuses->isEmpty()) {
                break;
            }
            
            // Batch update construction_paint_logs
            foreach($latestStatuses as $status) {
                $updateQuery = DB::table('construction_paint_logs')
                    ->where('iso_drawings', $status->iso_number)
                    ->where('spool', $status->spool_number);
                    
                if($filterIsoNumber) {
                    $updateQuery->where('iso_drawings', $filterIsoNumber);
                }

                if($filterSpoolNumber) {
                    $updateQuery->where('spool', $filterSpoolNumber);
                }
                
                $updated = $updateQuery->update(['spool_status' => $status->spool_status]);
                $totalUpdated += $updated;
            }
            
            $offset += $batchSize;
            
            // Memory cleanup
            unset($latestStatuses);
            
        } while (true);
        
        Log::info("Paint logs sync completed using batch processing. Total updated: $totalUpdated");
    }
    
    // Performance monitoring - End timing and log results
    $paintSyncEndTime = microtime(true);
    $paintSyncDuration = round($paintSyncEndTime - $paintSyncStartTime, 2);
    
    // Log performance metrics
    $performanceLog = [
        'operation' => 'paint_logs_sync',
        'duration_seconds' => $paintSyncDuration,
        'weld_logs_processed' => $k,
        'paint_updates' => $updatePaint,
        'completed_updates' => $updateCompleted,
        'memory_usage_mb' => round(memory_get_peak_usage() / 1024 / 1024, 2),
        'timestamp' => simdi()
    ];
    
    Log::info("Performance metrics for spool status sync: " . json_encode($performanceLog));
    
    // Alert if performance is degraded (> 30 seconds for paint sync)
    if($paintSyncDuration > 30) {
        Log::warning("Paint sync performance degraded - took {$paintSyncDuration} seconds. Consider database maintenance or optimization.");
    }

?>