<?php

namespace App\Services\LineListTriggers\Triggers;

use App\Services\LineListTriggers\Base\BaseLineListTrigger;
use App\Models\PaintMatrix;
use App\Helpers\TransactionHelper;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Paint Follow Ups Sync Trigger
 * 
 * Most complex trigger - handles comprehensive Paint Follow Ups synchronization:
 * - Creates SHOP and FIELD paint follow up records from weld logs
 * - Handles painting cycle changes with date field protection
 * - Calculates volumes and temperatures
 * - Protects records with filled date fields from updates
 * - Manages HOLD status for old painting cycles
 */
class PaintFollowUpsSyncTrigger extends BaseLineListTrigger
{
    public function getName(): string
    {
        return 'Paint Follow Ups Sync';
    }
    
    public function getDependentFields(): array
    {
        return [
            
        ];
    }
    
    protected function process($lineListData, $beforeData, array $context): array
    {
        $lineList = $lineListData;
        
        // Skip processing if essential fields are missing
        if (empty($lineList->line_no)) {
            Log::debug("Paint Follow Ups sync skipped - line_number missing");
            return ['skipped' => true, 'reason' => 'line_no_empty'];
        }
        
        // Skip processing if painting_cycle is empty
        // Paint Follow Ups should only exist when there is a painting cycle
        if (empty($lineList->painting_cycle)) {
            Log::debug("Paint Follow Ups sync skipped - painting_cycle is empty", [
                'line_no' => $lineList->line_no
            ]);
            return ['skipped' => true, 'reason' => 'painting_cycle_empty'];
        }

        // Check if matching weld_logs record exists with same unit + line + fluid_code
        // Paint records should only be created if there's a corresponding weld log
        $matchingWeldLogExists = db("weld_logs")
            ->where("line_number", $lineList->line_no)
            ->where("design_area", $lineList->unit)
            ->where("fluid_code", $lineList->fluid_code)
            ->exists();
            
        if (!$matchingWeldLogExists) {
            Log::debug("Paint Follow Ups sync skipped - no matching weld_logs record found", [
                'line_no' => $lineList->line_no,
                'unit' => $lineList->unit,
                'fluid_code' => $lineList->fluid_code
            ]);
            return ['skipped' => true, 'reason' => 'no_matching_weld_log'];
        }

        // Get all weld logs with this line number, unit and fluid_code
        $resultFields = [
            'vt_result',
            'rt_result',
            'ut_result',
            'pt_result',
            'mt_result',
            'pmi_result',
            'ht_result',
            'ferrite_result'
        ];
        
        $allWeldLogs = db("weld_logs")
            ->where("line_number", $lineList->line_no)
            ->where("design_area", $lineList->unit)
            ->where("fluid_code", $lineList->fluid_code)
            ->where(function ($query) {
                $query->where("no_of_the_joint_as_per_as_built_survey", "not like", "%clone%");
            })
            ->where(function ($query) use ($resultFields) {
                foreach ($resultFields as $field) {
                    $query->where(function ($subQuery) use ($field) {
                        $subQuery->whereNull($field)
                            ->orWhere($field, '')
                            ->orWhere($field, 'Accept / Годен');
                    });
                }
            })
            ->orderBy('id', 'ASC') // Deadlock prevention
            ->get();
            
        Log::debug("Syncing " . count($allWeldLogs) . " weld logs for line: " . $lineList->line_no);
        
        // Get or create paint matrix for this line
        // CRITICAL: Must include paint_cycle to get the correct matrix after painting_cycle changes
        $paintMatrix = PaintMatrix::where('line', $lineList->line_no)
            ->where('fluid_code', $lineList->fluid_code)
            ->where('area', $lineList->unit)
            ->where('paint_cycle', $lineList->painting_cycle)
            ->first();
            
        Log::debug("Paint Matrix data: " . json_encode($paintMatrix));

        if (!$paintMatrix) {
            $paintMatrixData = [
                'project' => $allWeldLogs->first()->project ?? '',
                'area' => $lineList->unit,
                'description' => "PIPE",
                'line' => $lineList->line_no,
                'fluid_code_description' => $lineList->fluid_ru,
                'fluid_code' => $lineList->fluid_code,
                'design_temperature' => $lineList->design_temperature,
                'operation_temperature' => $lineList->working_temperature,
                'paint_cycle' => $lineList->painting_cycle,
                'created_at' => now(),
                'updated_at' => now()
            ];
            
            $paintMatrix = PaintMatrix::create($paintMatrixData);
            Log::debug("Created new Paint Matrix for line: {$lineList->line_no}");
        }
        
        // Load temperature settings
        $temperatures = j(setting("temperatures"));
        $todayTempData = null;
        
        // Get blasting_date from construction_paint_logs
        $blastingDateRecord = db('construction_paint_logs')
            ->where('line', $lineList->line_no)
            ->whereNotNull('blasting_date')
            ->first();
            
        if (!is_null($temperatures) && $blastingDateRecord && !empty($blastingDateRecord->blasting_date)) {
            $blastingDate = Carbon::parse($blastingDateRecord->blasting_date);
            $dayOfYear = $blastingDate->format('z');
            $todayTempData = $temperatures[$dayOfYear] ?? null;
            
            Log::debug('Temperature data loaded', [
                'blasting_date' => $blastingDateRecord->blasting_date,
                'day_of_year' => $dayOfYear
            ]);
        }
        
        // Calculate volumes
        $fAvgNps = $allWeldLogs->where("type_of_joint", "F")->avg("nps_1");
        $sAvgNps = $allWeldLogs->where("type_of_joint", "S")->avg("nps_1");
        
        $mtoTotal = db("m_t_o_s")
            ->where("description_en", "like", "%pipe%")
            ->where("line", $lineList->line_no)
            ->selectRaw("SUM(quantity * POWER(odmm_1/2000, 2) * PI()) AS total")
            ->first()->total ?? 0;
        
        $fVolume = round(pi() * pow($fAvgNps, 2) * 200, 2);
        $sVolume = round($mtoTotal, 2);
        
        Log::debug('Volume calculations:', [
            'fVolume' => $fVolume,
            'sVolume' => $sVolume
        ]);
        
        $paintFollowUpCreated = 0;
        $paintFollowUpUpdated = 0;
        $processedPaintFollowUpIds = [];
        
        // Process weld logs in chunks with TransactionHelper
        TransactionHelper::chunkTransaction(
            $allWeldLogs,
            function ($weldLogChunk) use (
                $lineList, 
                $paintMatrix, 
                $todayTempData, 
                $sVolume, 
                $fVolume,
                &$paintFollowUpCreated, 
                &$paintFollowUpUpdated,
                &$processedPaintFollowUpIds
            ) {
                foreach ($weldLogChunk as $weldLog) {
                    // Skip if fluid code is empty
                    if (empty($weldLog->fluid_code)) {
                        continue;
                    }
                    
                    // Base data for paint follow up
                    $basePaintFollowUpData = [
                        'project' => $weldLog->project ?? '',
                        'description' => "PIPE",
                        'area' => $lineList->unit ?? '',
                        'line' => $lineList->line_no,
                        'iso_number' => $weldLog->iso_number ?? '',
                        'fluid_code' => $lineList->fluid_code,
                        'fluid_code_description' => $lineList->fluid_ru ?? '',
                        'cycle' => $lineList->painting_cycle ?? '',
                        'primer_coat_name_1' => $paintMatrix->primer_coat ?? '',
                        'brend_name_1' => $paintMatrix->brend_name_1 ?? '',
                        'colour_1' => $paintMatrix->colour_1 ?? '',
                        'ral_code_1' => $paintMatrix->ral_code_1 ?? '',
                        'thickness_1' => $paintMatrix->thickness_1 ?? '',
                        'intermediate_coat_name_2' => $paintMatrix->intermediate_coat ?? '',
                        'brend_name_2' => $paintMatrix->brend_name_2 ?? '',
                        'colour_2' => $paintMatrix->colour_2 ?? '',
                        'ral_code_2' => $paintMatrix->ral_code_2 ?? '',
                        'thickness_2' => $paintMatrix->thickness_2 ?? '',
                        'final_coat_name_3' => $paintMatrix->final_coat ?? '',
                        'brend_name_3' => $paintMatrix->brend_name_3 ?? '',
                        'colour_3' => $paintMatrix->colour_3 ?? '',
                        'ral_code_3' => $paintMatrix->ral_code_3 ?? '',
                        'thickness_3' => $paintMatrix->thickness_3 ?? '',
                        'surface_roughness' => $paintMatrix->surface_preparation ?? '',
                        'status' => 'In Progress',
                        'updated_at' => now()
                    ];
                    
                    // Process SHOP entry (using spool_number)
                    $result = $this->processShopRecord(
                        $lineList,
                        $weldLog,
                        $basePaintFollowUpData,
                        $paintMatrix,
                        $todayTempData,
                        $sVolume,
                        $paintFollowUpCreated,
                        $paintFollowUpUpdated
                    );
                    $paintFollowUpCreated = $result['created'];
                    $paintFollowUpUpdated = $result['updated'];
                    
                    // Process FIELD entry (using joint number)
                    $result = $this->processFieldRecord(
                        $lineList,
                        $weldLog,
                        $basePaintFollowUpData,
                        $paintMatrix,
                        $todayTempData,
                        $fVolume,
                        $paintFollowUpCreated,
                        $paintFollowUpUpdated,
                        $processedPaintFollowUpIds
                    );
                    $paintFollowUpCreated = $result['created'];
                    $paintFollowUpUpdated = $result['updated'];
                    $processedPaintFollowUpIds = $result['processed_ids'];
                }
                return $weldLogChunk->count();
            },
            1, // Chunk size
            10000 // 10ms delay
        );
        
        Log::debug("Paint Follow Ups sync completed: $paintFollowUpCreated created, $paintFollowUpUpdated updated");
        
        return [
            'success' => true,
            'created' => $paintFollowUpCreated,
            'updated' => $paintFollowUpUpdated
        ];
    }
    
    /**
     * Process SHOP paint follow up record
     */
    protected function processShopRecord(
        $lineList,
        $weldLog,
        $basePaintFollowUpData,
        $paintMatrix,
        $todayTempData,
        $sVolume,
        $paintFollowUpCreated,
        $paintFollowUpUpdated
    ): array {
        // Check if line has shop joints
        $hasShopJoint = db("weld_logs")
            ->where('line_number', $lineList->line_no)
            ->where('spool_number', $weldLog->spool_number)
            ->where('type_of_joint', 'S')
            ->exists();
        
        if (!$hasShopJoint || empty($weldLog->spool_number)) {
            return ['created' => $paintFollowUpCreated, 'updated' => $paintFollowUpUpdated];
        }
        
        // Prepare shop data
        $shopData = $basePaintFollowUpData;
        $shopData['location'] = 'SHOP';
        $shopData['spool_no_joint_no'] = $weldLog->spool_number;
        $shopData['surface_roughness'] = $paintMatrix->surface_preparation ?? '';
        
        // Add temperature and volume data
        if ($todayTempData) {
            $shopData['substrate_temprature'] = $todayTempData['temp_material_shop'];
            $shopData['ambient_temprature'] = $todayTempData['shop_ambient'];
        }
        $shopData['volume_1'] = $sVolume;
        $shopData['volume_2'] = $sVolume;
        $shopData['volume_3'] = $sVolume;
        $shopData['total_volume'] = $sVolume * 3;
        
        // Unique constraint for SHOP records
        $shopUniqueConstraintCondition = [
            'line' => $lineList->line_no,
            'spool_no_joint_no' => $weldLog->spool_number,
            'cycle' => $lineList->painting_cycle,
            'location' => 'SHOP'
        ];
        
        $shopWhereCondition = [
            'line' => $lineList->line_no,
            'spool_no_joint_no' => $weldLog->spool_number,
            'location' => 'SHOP'
        ];
        
        // Find existing record
        $existingShopRecord = db("paint_follow_ups")
            ->where($shopUniqueConstraintCondition)
            ->first();
        
        // Check for records with different painting cycle
        $recordsWithDifferentPaintCycle = db("paint_follow_ups")
            ->where($shopWhereCondition)
            ->where('cycle', '!=', $lineList->painting_cycle)
            ->get();
        
        if ($existingShopRecord) {
            // Update existing record with same painting cycle
            $hasAllEmptyDates = $this->hasAllEmptyDates($existingShopRecord);
            
            if ($hasAllEmptyDates) {
                db("paint_follow_ups")
                    ->where('id', $existingShopRecord->id)
                    ->update($shopData);
                $paintFollowUpUpdated++;
                Log::debug("Updated SHOP paint follow up {$existingShopRecord->id} - all dates were empty");
            } else {
                // Update only temperature/volume if empty
                $tempVolumeData = $this->getTempVolumeUpdateData(
                    $existingShopRecord,
                    $todayTempData,
                    $sVolume,
                    'SHOP'
                );
                
                if (!empty($tempVolumeData)) {
                    db("paint_follow_ups")
                        ->where('id', $existingShopRecord->id)
                        ->update($tempVolumeData);
                    Log::debug("Updated temp/volume for SHOP record {$existingShopRecord->id}");
                }
            }
        } else {
            // Handle records with different painting cycles
            if ($recordsWithDifferentPaintCycle->count() > 0) {
                foreach ($recordsWithDifferentPaintCycle as $oldRecord) {
                    $hasAnyDateFilled = !$this->hasAllEmptyDates($oldRecord);
                    
                    if ($hasAnyDateFilled) {
                        // Mark as HOLD
                        db("paint_follow_ups")
                            ->where('id', $oldRecord->id)
                            ->update([
                                'status' => 'HOLD',
                                'updated_at' => now()
                            ]);
                        Log::debug("Marked old SHOP record as HOLD");
                    } else {
                        // Update to new painting cycle
                        db("paint_follow_ups")
                            ->where('id', $oldRecord->id)
                            ->update([
                                'cycle' => $lineList->painting_cycle,
                                'updated_at' => now()
                            ]);
                        $paintFollowUpUpdated++;
                        Log::debug("Updated old SHOP record to new painting cycle");
                        return ['created' => $paintFollowUpCreated, 'updated' => $paintFollowUpUpdated];
                    }
                }
            }
            
            // Create new record
            $newShopData = $shopData;
            $newShopData['primer_coating_start_date'] = null;
            $newShopData['primer_coating_finish_date'] = null;
            $newShopData['start_intermediate_date2'] = null;
            $newShopData['finish_intermediate_date2'] = null;
            $newShopData['final_coat_start_date3'] = null;
            $newShopData['final_coat_finish_date3'] = null;
            $newShopData['created_at'] = now();
            $newShopData['updated_at'] = now();
            
            db("paint_follow_ups")->insert($newShopData);
            $paintFollowUpCreated++;
            Log::debug("Created new SHOP Paint Follow Up");
        }
        
        return ['created' => $paintFollowUpCreated, 'updated' => $paintFollowUpUpdated];
    }
    
    /**
     * Process FIELD paint follow up record
     */
    protected function processFieldRecord(
        $lineList,
        $weldLog,
        $basePaintFollowUpData,
        $paintMatrix,
        $todayTempData,
        $fVolume,
        $paintFollowUpCreated,
        $paintFollowUpUpdated,
        $processedPaintFollowUpIds
    ): array {
        if (empty($weldLog->no_of_the_joint_as_per_as_built_survey)) {
            return [
                'created' => $paintFollowUpCreated,
                'updated' => $paintFollowUpUpdated,
                'processed_ids' => $processedPaintFollowUpIds
            ];
        }
        
        $jointNo = $weldLog->no_of_the_joint_as_per_as_built_survey;
        
        $fieldData = $basePaintFollowUpData;
        $fieldData['location'] = 'FIELD';
        $fieldData['spool_no_joint_no'] = $jointNo;
        $fieldData['surface_roughness'] = $paintMatrix->touch_up_of_damaged_parts ?? '';
        
        // Add temperature and volume data
        if ($todayTempData) {
            $fieldData['substrate_temprature'] = $todayTempData['temp_material_field'];
            $fieldData['ambient_temprature'] = $todayTempData['field_ambient'];
        }
        $fieldData['volume_1'] = $fVolume;
        $fieldData['volume_2'] = $fVolume;
        $fieldData['volume_3'] = $fVolume;
        $fieldData['total_volume'] = $fVolume * 3;
        
        // Unique constraint for FIELD records
        $fieldUniqueConstraintCondition = [
            'line' => $lineList->line_no,
            'spool_no_joint_no' => $jointNo,
            'cycle' => $lineList->painting_cycle,
            'location' => 'FIELD'
        ];
        
        $fieldWhereCondition = [
            'line' => $lineList->line_no,
            'spool_no_joint_no' => $jointNo,
            'location' => 'FIELD'
        ];
        
        // Find existing record
        $existingFieldRecord = db("paint_follow_ups")
            ->where($fieldUniqueConstraintCondition)
            ->first();
        
        // Check for records with different painting cycle
        $recordsWithDifferentPaintCycle = db("paint_follow_ups")
            ->where($fieldWhereCondition)
            ->where('cycle', '!=', $lineList->painting_cycle)
            ->get();
            
        if ($existingFieldRecord) {
            // Skip if already processed
            if (in_array($existingFieldRecord->id, $processedPaintFollowUpIds)) {
                return [
                    'created' => $paintFollowUpCreated,
                    'updated' => $paintFollowUpUpdated,
                    'processed_ids' => $processedPaintFollowUpIds
                ];
            }
            
            $processedPaintFollowUpIds[] = $existingFieldRecord->id;
            
            $hasAllEmptyDates = $this->hasAllEmptyDates($existingFieldRecord);
            
            if ($hasAllEmptyDates) {
                db("paint_follow_ups")
                    ->where('id', $existingFieldRecord->id)
                    ->update($fieldData);
                $paintFollowUpUpdated++;
                Log::debug("Updated FIELD paint follow up {$existingFieldRecord->id} - all dates were empty");
            } else {
                // Update only temperature/volume if empty
                $tempVolumeData = $this->getTempVolumeUpdateData(
                    $existingFieldRecord,
                    $todayTempData,
                    $fVolume,
                    'FIELD'
                );
                
                if (!empty($tempVolumeData)) {
                    db("paint_follow_ups")
                        ->where('id', $existingFieldRecord->id)
                        ->update($tempVolumeData);
                    Log::debug("Updated temp/volume for FIELD record {$existingFieldRecord->id}");
                }
            }
        } else {
            // Handle records with different painting cycles
            if ($recordsWithDifferentPaintCycle->count() > 0) {
                foreach ($recordsWithDifferentPaintCycle as $oldRecord) {
                    $hasAnyDateFilled = !$this->hasAllEmptyDates($oldRecord);
                    
                    if ($hasAnyDateFilled) {
                        // Mark as HOLD
                        db("paint_follow_ups")
                            ->where('id', $oldRecord->id)
                            ->update([
                                'status' => 'HOLD',
                                'updated_at' => now()
                            ]);
                        Log::debug("Marked old FIELD record as HOLD");
                    } else {
                        // Update to new painting cycle
                        db("paint_follow_ups")
                            ->where('id', $oldRecord->id)
                            ->update([
                                'cycle' => $lineList->painting_cycle,
                                'updated_at' => now()
                            ]);
                        $paintFollowUpUpdated++;
                        Log::debug("Updated old FIELD record to new painting cycle");
                        return [
                            'created' => $paintFollowUpCreated,
                            'updated' => $paintFollowUpUpdated,
                            'processed_ids' => $processedPaintFollowUpIds
                        ];
                    }
                }
            }
            
            // Create new record
            $newFieldData = $fieldData;
            $newFieldData['primer_coating_start_date'] = null;
            $newFieldData['primer_coating_finish_date'] = null;
            $newFieldData['start_intermediate_date2'] = null;
            $newFieldData['finish_intermediate_date2'] = null;
            $newFieldData['final_coat_start_date3'] = null;
            $newFieldData['final_coat_finish_date3'] = null;
            $newFieldData['created_at'] = now();
            $newFieldData['updated_at'] = now();
            
            db("paint_follow_ups")->insert($newFieldData);
            $paintFollowUpCreated++;
            Log::debug("Created new FIELD Paint Follow Up");
        }
        
        return [
            'created' => $paintFollowUpCreated,
            'updated' => $paintFollowUpUpdated,
            'processed_ids' => $processedPaintFollowUpIds
        ];
    }
    
    /**
     * Check if all date fields are empty
     */
    protected function hasAllEmptyDates($record): bool
    {
        return empty($record->primer_coating_start_date) && 
               empty($record->primer_coating_finish_date) && 
               empty($record->start_intermediate_date2) && 
               empty($record->finish_intermediate_date2) &&
               empty($record->final_coat_start_date3) &&
               empty($record->final_coat_finish_date3);
    }
    
    /**
     * Get temperature and volume update data for records with filled dates
     */
    protected function getTempVolumeUpdateData($record, $todayTempData, $volume, $location): array
    {
        $updateData = [];
        
        // Add temperature data if available and field is empty
        if ($todayTempData) {
            $tempField = $location === 'SHOP' ? 'temp_material_shop' : 'temp_material_field';
            $ambientField = $location === 'SHOP' ? 'shop_ambient' : 'field_ambient';
            
            if (empty($record->substrate_temprature)) {
                $updateData['substrate_temprature'] = $todayTempData[$tempField];
            }
            if (empty($record->ambient_temprature)) {
                $updateData['ambient_temprature'] = $todayTempData[$ambientField];
            }
        }
        
        // Add volume data if fields are empty
        if (empty($record->volume_1)) {
            $updateData['volume_1'] = $volume;
        }
        if (empty($record->volume_2)) {
            $updateData['volume_2'] = $volume;
        }
        if (empty($record->volume_3)) {
            $updateData['volume_3'] = $volume;
        }
        if (empty($record->total_volume)) {
            $updateData['total_volume'] = $volume * 3;
        }
        
        return $updateData;
    }
}