<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class FixPrepareProductsPricingController extends Controller
{
    private $logFileName;
    private $logFilePath;

    /**
     * Initialize log file with current date and time
     */
    private function initializeLogFile()
    {
        // Only initialize once
        if (!empty($this->logFilePath)) {
            return;
        }
        
        $timestamp = now()->format('dmYHi'); // Format: 11102025-0853
        $this->logFileName = "{$timestamp}.log";
        $this->logFilePath = storage_path("logs/pricing_fixes/{$this->logFileName}");
        
        // Create directory if it doesn't exist
        $directory = storage_path('logs/pricing_fixes');
        if (!file_exists($directory)) {
            mkdir($directory, 0755, true);
        }
        
        // Write header to log file
        $this->writeLogDirect("----------------------------------------------------------------");
        $this->writeLogDirect("PRICING FIX LOG");
        $this->writeLogDirect("Started at: " . now()->format('Y-m-d H:i:s'));
        $this->writeLogDirect("Log file: {$this->logFileName}");
        $this->writeLogDirect("----------------------------------------------------------------");
        $this->writeLogDirect("");
    }

    /**
     * Write to log file directly (used during initialization)
     */
    private function writeLogDirect($message)
    {
        file_put_contents($this->logFilePath, $message . PHP_EOL, FILE_APPEND);
    }

    /**
     * Write to custom log file
     */
    private function writeLog($message)
    {
        // Ensure log file is initialized
        if (empty($this->logFilePath)) {
            $this->initializeLogFile();
        }
        
        file_put_contents($this->logFilePath, $message . PHP_EOL, FILE_APPEND);
    }

    /**
     * Fix incorrect pricing records in nst_prepare_products
     */
    public function fixPricingRecords()
    {
        // Initialize custom log file
        $this->initializeLogFile();
        
        try {
            DB::beginTransaction();

            // Get all records with pricing discrepancies
            $discrepantRecords = DB::table('nst_prepare_products')
                ->leftJoin('purchase_lines', 'purchase_lines.id', '=', 'nst_prepare_products.purchase_lines_id')
                ->select(
                    'nst_prepare_products.id',
                    'nst_prepare_products.transaction_id',
                    'nst_prepare_products.purchase_lines_id',
                    'nst_prepare_products.variation_id',
                    'nst_prepare_products.product_id',
                    'nst_prepare_products.unit_price_after_shipping as old_price',
                    'nst_prepare_products.transfer_quantity',
                    'purchase_lines.purchase_price_inc_tax as correct_price'
                )
                ->where('nst_prepare_products.value_per_unit', 0)
                ->where('nst_prepare_products.status', 'complete')
                ->whereColumn('nst_prepare_products.unit_price_after_shipping', '!=', 'purchase_lines.purchase_price_inc_tax')
                ->orderBy('nst_prepare_products.id', 'DESC')
                ->get();

            if ($discrepantRecords->isEmpty()) {
                $this->writeLog('No discrepant records found for pricing fix');
                return response()->json([
                    'success' => true,
                    'message' => 'No discrepant records found',
                    'updated_count' => 0,
                    'log_file' => $this->logFileName
                ]);
            }

            $this->writeLog('========================================');
            $this->writeLog('BATCH PRICING FIX STARTED');
            $this->writeLog('Total records to fix: ' . $discrepantRecords->count());
            $this->writeLog('========================================');

            $updatedCount = 0;
            $errors = [];

            foreach ($discrepantRecords as $record) {
                try {
                    $this->fixSingleRecord($record);
                    $updatedCount++;
                } catch (\Exception $e) {
                    $errors[] = [
                        'id' => $record->id,
                        'transaction_id' => $record->transaction_id,
                        'error' => $e->getMessage()
                    ];
                    
                    $this->writeLog("ERROR fixing record {$record->id}: " . $e->getMessage());
                }
            }

            DB::commit();

            $this->writeLog('========================================');
            $this->writeLog("BATCH PRICING FIX COMPLETED - Updated {$updatedCount} records");
            $this->writeLog("Completed at: " . now()->format('Y-m-d H:i:s'));
            $this->writeLog('========================================');

            return response()->json([
                'success' => true,
                'message' => "Successfully updated {$updatedCount} records",
                'updated_count' => $updatedCount,
                'total_found' => $discrepantRecords->count(),
                'errors' => $errors,
                'log_file' => $this->logFileName,
                'log_path' => $this->logFilePath
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            
            $this->writeLog('CRITICAL ERROR in fixPricingRecords: ' . $e->getMessage());
            
            return response()->json([
                'success' => false,
                'message' => 'Error updating records: ' . $e->getMessage(),
                'log_file' => $this->logFileName
            ], 500);
        }
    }

    /**
     * Fix a single record with all related updates and detailed logging
     */
    private function fixSingleRecord($record)
    {
        $correctPrice = $record->correct_price;
        $oldPrice = $record->old_price;
        $transactionId = $record->transaction_id;
        $purchaseLineId = $record->purchase_lines_id;
        $variationId = $record->variation_id;
        $productId = $record->product_id;
        $transferQuantity = $record->transfer_quantity ?? 1;

        $this->writeLog('');
        $this->writeLog('----------------------------------------------------------------');
        $this->writeLog("STARTING FIX FOR nst_prepare_products.id: {$record->id}");
        $this->writeLog('----------------------------------------------------------------');
        $this->writeLog("Transaction ID: {$transactionId}");
        $this->writeLog("Product ID: {$productId}");
        $this->writeLog("Old Price: {$oldPrice} ? New Price: {$correctPrice}");
        $this->writeLog("Transfer Quantity: {$transferQuantity}");

        // Calculate totals
        $oldTotal = $transferQuantity * $oldPrice;
        $newTotal = $transferQuantity * $correctPrice;
        $difference = $newTotal - $oldTotal;
        $this->writeLog("Price Difference: {$difference} (Old Total: {$oldTotal} ? New Total: {$newTotal})");
        $this->writeLog('----------------------------------------------------------------');

        // 1. LOG & UPDATE transaction_sell_lines
        $this->writeLog('[1] TRANSACTION_SELL_LINES:');
        $sellLinesBefore = DB::table('transaction_sell_lines')
            ->where('transaction_id', $transactionId)
            ->where('variation_id', $variationId)
            ->get();

        if ($sellLinesBefore->isNotEmpty()) {
            foreach ($sellLinesBefore as $line) {
                $this->writeLog("  BEFORE (ID: {$line->id}):");
                $this->writeLog("    - unit_price_before_discount: {$line->unit_price_before_discount}");
                $this->writeLog("    - unit_price: {$line->unit_price}");
                $this->writeLog("    - unit_price_inc_tax: {$line->unit_price_inc_tax}");
            }

            DB::table('transaction_sell_lines')
                ->where('transaction_id', $transactionId)
                ->where('variation_id', $variationId)
                ->update([
                    'unit_price_before_discount' => $correctPrice,
                    'unit_price' => $correctPrice,
                    'unit_price_inc_tax' => $correctPrice,
                    'updated_at' => now()
                ]);

            $this->writeLog("  AFTER:");
            $this->writeLog("    - unit_price_before_discount: {$correctPrice}");
            $this->writeLog("    - unit_price: {$correctPrice}");
            $this->writeLog("    - unit_price_inc_tax: {$correctPrice}");
            $this->writeLog("  ? Updated " . $sellLinesBefore->count() . " record(s)");
        } else {
            $this->writeLog("  ? No transaction_sell_lines found for transaction_id: {$transactionId}, product_id: {$productId} , variation_id: {$variationId}");
        }
        $this->writeLog('----------------------------------------------------------------');

        // 2. LOG & UPDATE purchase_lines (child transaction)
        $this->writeLog('[2] PURCHASE_LINES (Child Transaction):');
        $purchaseTransaction = DB::table('transactions')
            ->where('transfer_parent_id', $transactionId)
            ->select('id')
            ->first();

        if ($purchaseTransaction) {
            $purchaseTransactionId = $purchaseTransaction->id;
            $this->writeLog("  Child Transaction ID: {$purchaseTransactionId}");

            $purchaseLinesBefore = DB::table('purchase_lines')
                ->where('transaction_id', $purchaseTransactionId)
                ->where('variation_id', $variationId)
                ->get();

            if ($purchaseLinesBefore->isNotEmpty()) {
                foreach ($purchaseLinesBefore as $line) {
                    $this->writeLog("  BEFORE (ID: {$line->id}):");
                    $this->writeLog("    - pp_without_discount: {$line->pp_without_discount}");
                    $this->writeLog("    - purchase_price: {$line->purchase_price}");
                    $this->writeLog("    - purchase_price_inc_tax: {$line->purchase_price_inc_tax}");
                }

                DB::table('purchase_lines')
                    ->where('transaction_id', $purchaseTransactionId)
                    ->where('variation_id', $variationId)
                    ->update([
                        'pp_without_discount' => $correctPrice,
                        'purchase_price' => $correctPrice,
                        'purchase_price_inc_tax' => $correctPrice,
                        'updated_at' => now()
                    ]);

                $this->writeLog("  AFTER:");
                $this->writeLog("    - pp_without_discount: {$correctPrice}");
                $this->writeLog("    - purchase_price: {$correctPrice}");
                $this->writeLog("    - purchase_price_inc_tax: {$correctPrice}");
                $this->writeLog("  ? Updated " . $purchaseLinesBefore->count() . " record(s)");
            } else {
                $this->writeLog("  ? No purchase_lines found for transaction_id: {$purchaseTransactionId}, product_id: {$productId} , variation_id: {$variationId}");
            }
        } else {
            $this->writeLog("  ? No child transaction found with transfer_parent_id: {$transactionId}");
        }
        $this->writeLog('----------------------------------------------------------------');

        // 3. LOG & UPDATE nst_prepare_products
        $this->writeLog('[3] NST_PREPARE_PRODUCTS:');
        $this->writeLog("  BEFORE (ID: {$record->id}):");
        $this->writeLog("    - unit_price_after_shipping: {$oldPrice}");

        DB::table('nst_prepare_products')
            ->where('id', $record->id)
            ->update([
                'unit_price_after_shipping' => $correctPrice,
                'update_file_path' => $this->logFileName,   
                'updated_at' => now()
                
            ]);

        $this->writeLog("  AFTER:");
        $this->writeLog("    - unit_price_after_shipping: {$correctPrice}");
        $this->writeLog("  ? Updated");
        $this->writeLog('----------------------------------------------------------------');

        // 4. LOG & UPDATE transactions (main transaction)
        $this->writeLog('[4] TRANSACTIONS (Main Transaction):');
        $mainTransactionBefore = DB::table('transactions')
            ->where('id', $transactionId)
            ->select('id', 'final_total')
            ->first();

        if ($mainTransactionBefore) {
            $this->writeLog("  BEFORE (ID: {$mainTransactionBefore->id}):");
            $this->writeLog("    - final_total: {$mainTransactionBefore->final_total}");
            $this->writeLog("    - adjustment: +{$difference}");

            DB::table('transactions')
                ->where('id', $transactionId)
                ->update([
                    'final_total' => DB::raw("final_total + ({$difference})"),
                    'updated_at' => now()
                ]);

            $newFinalTotal = $mainTransactionBefore->final_total + $difference;
            $this->writeLog("  AFTER:");
            $this->writeLog("    - final_total: {$newFinalTotal}");
            $this->writeLog("  ? Updated");
        } else {
            $this->writeLog("  ? Main transaction not found: {$transactionId}");
        }
        $this->writeLog('----------------------------------------------------------------');

        // 5. LOG & UPDATE transactions (child transactions with transfer_parent_id)
        $this->writeLog('[5] TRANSACTIONS (Child Transactions):');
        $childTransactionsBefore = DB::table('transactions')
            ->where('transfer_parent_id', $transactionId)
            ->select('id', 'final_total')
            ->get();

        if ($childTransactionsBefore->isNotEmpty()) {
            foreach ($childTransactionsBefore as $childTrans) {
                $this->writeLog("  BEFORE (ID: {$childTrans->id}):");
                $this->writeLog("    - final_total: {$childTrans->final_total}");
                $this->writeLog("    - adjustment: +{$difference}");
                
                $newChildFinalTotal = $childTrans->final_total + $difference;
                $this->writeLog("  AFTER:");
                $this->writeLog("    - final_total: {$newChildFinalTotal}");
            }

            DB::table('transactions')
                ->where('transfer_parent_id', $transactionId)
                ->update([
                    'final_total' => DB::raw("final_total + ({$difference})"),
                    'updated_at' => now()
                ]);

            $this->writeLog("  ? Updated " . $childTransactionsBefore->count() . " child transaction(s)");
        } else {
            $this->writeLog("  ? No child transactions found");
        }

        $this->writeLog('----------------------------------------------------------------');
        $this->writeLog("? COMPLETED FIX FOR nst_prepare_products.id: {$record->id}");
        $this->writeLog('----------------------------------------------------------------');
        $this->writeLog('');
    }

    /**
     * Preview records that will be updated (dry run)
     */
    public function previewFixRecords()
    {
        $discrepantRecords = DB::table('nst_prepare_products')
            ->leftJoin('purchase_lines', 'purchase_lines.id', '=', 'nst_prepare_products.purchase_lines_id')
            ->leftJoin('transactions', 'transactions.id', '=', 'nst_prepare_products.transaction_id')
            ->select(
                'nst_prepare_products.id',
                'nst_prepare_products.transaction_id',
                'transactions.ref_no',
                'nst_prepare_products.variation_id',
                'nst_prepare_products.product_id',
                'nst_prepare_products.unit_price_after_shipping as current_price',
                'nst_prepare_products.transfer_quantity',
                'purchase_lines.purchase_price_inc_tax as correct_price',
                DB::raw('(nst_prepare_products.transfer_quantity * nst_prepare_products.unit_price_after_shipping) as current_total'),
                DB::raw('(nst_prepare_products.transfer_quantity * purchase_lines.purchase_price_inc_tax) as correct_total'),
                DB::raw('((nst_prepare_products.transfer_quantity * purchase_lines.purchase_price_inc_tax) - (nst_prepare_products.transfer_quantity * nst_prepare_products.unit_price_after_shipping)) as difference')
            )
            ->where('nst_prepare_products.value_per_unit', 0)
            ->where('nst_prepare_products.status', 'complete')
            ->whereColumn('nst_prepare_products.unit_price_after_shipping', '!=', 'purchase_lines.purchase_price_inc_tax')
            ->orderBy('nst_prepare_products.id', 'DESC')
            ->get();

        return response()->json([
            'success' => true,
            'total_records' => $discrepantRecords->count(),
            'records' => $discrepantRecords
        ]);
    }

    /**
     * Fix a specific record by ID
     */
    public function fixSpecificRecord($id)
    {
        // Initialize custom log file
        $this->initializeLogFile();
        
        try {
            DB::beginTransaction();

            $record = DB::table('nst_prepare_products')
                ->leftJoin('purchase_lines', 'purchase_lines.id', '=', 'nst_prepare_products.purchase_lines_id')
                ->select(
                    'nst_prepare_products.id',
                    'nst_prepare_products.transaction_id',
                    'nst_prepare_products.purchase_lines_id',
                    'nst_prepare_products.variation_id',
                    'nst_prepare_products.product_id',
                    'nst_prepare_products.unit_price_after_shipping as old_price',
                    'nst_prepare_products.transfer_quantity',
                    'purchase_lines.purchase_price_inc_tax as correct_price'
                )
                ->where('nst_prepare_products.id', $id)
                ->whereColumn('nst_prepare_products.unit_price_after_shipping', '!=', 'purchase_lines.purchase_price_inc_tax')
                ->first();

            if (!$record) {
                $this->writeLog("Record not found or prices already match for nst_prepare_products.id: {$id}");
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found or prices already match',
                    'log_file' => $this->logFileName
                ], 404);
            }

            $this->fixSingleRecord($record);

            DB::commit();

            $this->writeLog('');
            $this->writeLog("? Successfully updated record ID: {$id}");
            $this->writeLog("Completed at: " . now()->format('Y-m-d H:i:s'));

            return response()->json([
                'success' => true,
                'message' => "Successfully updated record ID: {$id}",
                'record' => $record,
                'log_file' => $this->logFileName,
                'log_path' => $this->logFilePath
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            
            $this->writeLog("CRITICAL ERROR fixing specific record {$id}: " . $e->getMessage());
            $this->writeLog("Stack trace: " . $e->getTraceAsString());
            
            return response()->json([
                'success' => false,
                'message' => 'Error updating record: ' . $e->getMessage(),
                'log_file' => $this->logFileName,
                'log_path' => $this->logFilePath
            ], 500);
        }
    }
    
    /**
     * Fix pricing records where purchase_lines_id is null
     * Traces through: nst_prepare_products -> transaction_sell_lines -> transaction_sell_lines_purchase_lines -> purchase_lines
     */
    public function fixNullPurchaseLinesRecords()
    {
        // Initialize custom log file
        $this->initializeLogFile();
        
        try {
            DB::beginTransaction();

            // Get records where purchase_lines_id is null
            $nullRecords = DB::table('nst_prepare_products')
                ->select(
                    'nst_prepare_products.id',
                    'nst_prepare_products.transaction_id',
                    'nst_prepare_products.variation_id',
                    'nst_prepare_products.product_id',
                    'nst_prepare_products.unit_price_after_shipping as current_price',
                    'nst_prepare_products.transfer_quantity'
                )
                ->whereNull('nst_prepare_products.purchase_lines_id')
                ->whereNull('nst_prepare_products.deleted_at')
                // ->whereNull('nst_prepare_products.update_file_path')
                ->where('nst_prepare_products.status', 'complete')
                ->where('nst_prepare_products.transfer_quantity', '>' , 0)
                ->orderBy('nst_prepare_products.id', 'DESC')
                ->get();

            if ($nullRecords->isEmpty()) {
                $this->writeLog('No records found with null purchase_lines_id');
                return response()->json([
                    'success' => true,
                    'message' => 'No records found with null purchase_lines_id',
                    'updated_count' => 0,
                    'log_file' => $this->logFileName
                ]);
            }

            $this->writeLog('========================================');
            $this->writeLog('NULL PURCHASE_LINES_ID FIX STARTED');
            $this->writeLog('Total records to process: ' . $nullRecords->count());
            $this->writeLog('========================================');

            $updatedCount = 0;
            $skippedNoPurchaseLine = 0;
            $skippedMultiplePurchaseLines = 0;
            $skippedPriceMatch = 0;
           $errors = [];

            foreach ($nullRecords as $record) {
                try {
                    // Trace through relationships to find purchase_lines.id
                    $purchaseLines = DB::table('transaction_sell_lines')
                        ->join('transaction_sell_lines_purchase_lines', 
                            'transaction_sell_lines.id', '=', 'transaction_sell_lines_purchase_lines.sell_line_id')
                        ->join('purchase_lines', 
                            'transaction_sell_lines_purchase_lines.purchase_line_id', '=', 'purchase_lines.id')
                        ->select(
                            'purchase_lines.id as purchase_line_id',
                            'purchase_lines.purchase_price_inc_tax'
                        )
                        ->where('transaction_sell_lines.transaction_id', $record->transaction_id)
                        ->where('transaction_sell_lines.variation_id', $record->variation_id)
                        ->get();

                    if ($purchaseLines->isEmpty()) {
                        $this->writeLog("? Skipped record {$record->id}: Could not find purchase_line through relationships");
                        $skippedNoPurchaseLine++;
                        continue;
                    }

                    // Check if there are multiple purchase lines
                    if ($purchaseLines->count() > 1) {
                        $this->writeLog("? Record {$record->id} has multiple purchase lines ({$purchaseLines->count()} found)");
                        $this->writeLog("  Purchase Line IDs: " . $purchaseLines->pluck('purchase_line_id')->implode(', '));
                        
                        // Show prices for each purchase line
                        foreach ($purchaseLines as $pl) {
                            $this->writeLog("    - Purchase Line {$pl->purchase_line_id}: {$pl->purchase_price_inc_tax}");
                        }
                        
                        // Calculate weighted average price
                        $weightedPrice = $this->calculateWeightedAveragePrice(
                            $record->transaction_id,
                            $record->variation_id,
                            $record->transfer_quantity
                        );
                        
                        if ($weightedPrice !== null) {
                            $this->writeLog("  Calculated weighted average price: {$weightedPrice}");
                            
                            // Check if calculated price matches current price
                            if (abs($weightedPrice - $record->current_price) < 0.01) {
                                $this->writeLog("  ? Weighted price matches current price, only updating purchase_lines_id");
                                
                                // Just mark it as processed with the first purchase line
                                DB::table('nst_prepare_products')
                                    ->where('id', $record->id)
                                    ->update([
                                        // 'purchase_lines_id' => $purchaseLines->first()->purchase_line_id,
                                        'update_file_path' => 'multiple purchase lines weighted ' . $this->logFileName,
                                        'updated_at' => now()
                                    ]);
                                
                                $skippedPriceMatch++;
                            } else {
                                // Price needs fixing with weighted average
                                $this->writeLog("  ? Calling fixMultiplePurchaseLinesRecord with weighted price");
                                
                                $this->fixMultiplePurchaseLinesRecord(
                                    $record->id,
                                    $record->transaction_id,
                                    $record->variation_id,
                                    $record->product_id,
                                    $record->current_price,
                                    $weightedPrice,
                                    $record->transfer_quantity,
                                    $purchaseLines
                                );
                                
                                $updatedCount++;
                            }
                        } else {
                            // Could not calculate weighted price, mark as error
                            $this->writeLog("  ? Could not calculate weighted average price");
                            DB::table('nst_prepare_products')
                                ->where('id', $record->id)
                                ->update([
                                    'update_file_path' => 'many purchase lines error ' . $this->logFileName,
                                    'updated_at' => now()
                                ]);
                            
                            $skippedMultiplePurchaseLines++;
                        }
                        continue;
                    }

                    // Only one purchase line found - proceed with fix
                    $purchaseLine = $purchaseLines->first();

                    // Update purchase_lines_id in nst_prepare_products first
                    DB::table('nst_prepare_products')
                        ->where('id', $record->id)
                        ->update([
                            'purchase_lines_id' => $purchaseLine->purchase_line_id,
                            'updated_at' => now()
                        ]);

                    $this->writeLog("? Updated purchase_lines_id to {$purchaseLine->purchase_line_id} for record {$record->id}");

                    // Check if prices match - if they do, skip fixSingleRecord
                    if ($purchaseLine->purchase_price_inc_tax == $record->current_price) {
                        $this->writeLog("? Skipped record {$record->id}: Prices already match ({$record->current_price})");
                        $skippedPriceMatch++;
                        continue;
                    }

                    // Prices don't match, so call fixSingleRecord
                    $recordToFix = (object) [
                        'id' => $record->id,
                        'transaction_id' => $record->transaction_id,
                        'purchase_lines_id' => $purchaseLine->purchase_line_id,
                        'product_id' => $record->product_id,
                        'variation_id' => $record->variation_id,
                        'old_price' => $record->current_price,
                        'transfer_quantity' => $record->transfer_quantity,
                        'correct_price' => $purchaseLine->purchase_price_inc_tax
                    ];

                    // Update purchase_lines_id in nst_prepare_products before fixing
                    DB::table('nst_prepare_products')
                        ->where('id', $record->id)
                        ->update([
                            'purchase_lines_id' => $purchaseLine->purchase_line_id,
                            'updated_at' => now()
                        ]);

                    $this->writeLog("ℹ Updated purchase_lines_id to {$purchaseLine->purchase_line_id} for record {$record->id}");

                    // Now fix the pricing
                    $this->fixSingleRecord($recordToFix);
                    $updatedCount++;

                } catch (\Exception $e) {
                    $errors[] = [
                        'id' => $record->id,
                        'transaction_id' => $record->transaction_id,
                        'error' => $e->getMessage()
                    ];
                    
                    $this->writeLog("ERROR processing record {$record->id}: " . $e->getMessage());
                }
            }

            DB::commit();

            $totalSkipped = $skippedNoPurchaseLine + $skippedMultiplePurchaseLines + $skippedPriceMatch;

            $this->writeLog('========================================');
            $this->writeLog("NULL PURCHASE_LINES_ID FIX COMPLETED");
            $this->writeLog("Total processed: " . $nullRecords->count());
            $this->writeLog("Fixed (pricing updated): {$updatedCount}");
            $this->writeLog("Skipped breakdown:");
            $this->writeLog("  - No purchase line found: {$skippedNoPurchaseLine}");
            $this->writeLog("  - Multiple purchase lines: {$skippedMultiplePurchaseLines}");
            $this->writeLog("  - Price already matches: {$skippedPriceMatch}");
            $this->writeLog("  - Total skipped: {$totalSkipped}");
            $this->writeLog("Completed at: " . now()->format('Y-m-d H:i:s'));
            $this->writeLog('========================================');

            return response()->json([

                'success' => true,
                'message' => "Processing complete: {$updatedCount} fixed, {$totalSkipped} skipped",
                'total_found' => $nullRecords->count(),
                'updated_count' => $updatedCount,
                'skipped_breakdown' => [
                    'no_purchase_line' => $skippedNoPurchaseLine,
                    'multiple_purchase_lines' => $skippedMultiplePurchaseLines,
                    'price_match' => $skippedPriceMatch,
                    'total_skipped' => $totalSkipped
                ],
                'errors' => $errors,
                'log_file' => $this->logFileName,
                'log_path' => $this->logFilePath
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            
            $this->writeLog('CRITICAL ERROR in fixNullPurchaseLinesRecords: ' . $e->getMessage());
            
            return response()->json([
                'success' => false,
                'message' => 'Error processing records: ' . $e->getMessage(),
                'log_file' => $this->logFileName
            ], 500);
        }
    }

    /**
     * Preview records with null purchase_lines_id (dry run)
     */
    public function previewNullPurchaseLinesRecords()
    {
        $baseRecords = DB::table('nst_prepare_products')
            ->leftJoin('transactions', 'transactions.id', '=', 'nst_prepare_products.transaction_id')
            ->select(
                'nst_prepare_products.id',
                'nst_prepare_products.transaction_id',
                'transactions.ref_no',
                'nst_prepare_products.variation_id',
                'nst_prepare_products.product_id',
                'nst_prepare_products.unit_price_after_shipping as current_price',
                'nst_prepare_products.transfer_quantity'
            )
            ->whereNull('nst_prepare_products.purchase_lines_id')
            ->whereNull('nst_prepare_products.deleted_at')
            // ->whereNull('nst_prepare_products.update_file_path')
            ->where('nst_prepare_products.status', 'complete')
            ->where('nst_prepare_products.transfer_quantity', '>' , 0)
            ->orderBy('nst_prepare_products.id', 'DESC')
            ->get();

        $processedRecords = [];
        $noPurchaseLineCount = 0;
        $multiplePurchaseLinesCount = 0;
        $singlePurchaseLineCount = 0;
        $priceMatchCount = 0;
        $priceDiscrepancyCount = 0;

        foreach ($baseRecords as $record) {
            // Find all purchase lines for this record
            $purchaseLines = DB::table('transaction_sell_lines')
                ->join('transaction_sell_lines_purchase_lines', 
                    'transaction_sell_lines.id', '=', 'transaction_sell_lines_purchase_lines.sell_line_id')
                ->join('purchase_lines', 
                    'transaction_sell_lines_purchase_lines.purchase_line_id', '=', 'purchase_lines.id')
                ->select(
                    'purchase_lines.id as purchase_line_id',
                    'purchase_lines.purchase_price_inc_tax'
                )
                ->where('transaction_sell_lines.transaction_id', $record->transaction_id)
                ->where('transaction_sell_lines.variation_id', $record->variation_id)
                ->get();

            $purchaseLineCount = $purchaseLines->count();
            
            if ($purchaseLineCount == 0) {
                // No purchase line found
                $noPurchaseLineCount++;
                $processedRecords[] = [
                    'id' => $record->id,
                    'transaction_id' => $record->transaction_id,
                    'ref_no' => $record->ref_no,
                    'variation_id' => $record->variation_id,
                    'product_id' => $record->product_id,
                    'current_price' => $record->current_price,
                    'transfer_quantity' => $record->transfer_quantity,
                    'status' => 'no_purchase_line',
                    'purchase_line_count' => 0,
                    'found_purchase_line_ids' => null,
                    'correct_price' => null,
                    'current_total' => $record->transfer_quantity * $record->current_price,
                    'correct_total' => null,
                    'difference' => null
                ];
            } elseif ($purchaseLineCount > 1) {
                // Multiple purchase lines found
                $multiplePurchaseLinesCount++;
                // Calculate weighted average for preview
                $weightedPrice = $this->calculateWeightedAveragePrice(
                    $record->transaction_id,
                    $record->variation_id,
                    $record->transfer_quantity
                );
                
                $currentTotal = $record->transfer_quantity * $record->current_price;
                $correctTotal = $weightedPrice ? ($record->transfer_quantity * $weightedPrice) : null;
                $difference = ($weightedPrice && $correctTotal) ? ($correctTotal - $currentTotal) : null;
                
                $priceMatch = $weightedPrice ? (abs($weightedPrice - $record->current_price) < 0.01) : false;
                
                if ($priceMatch) {
                    $priceMatchCount++;
                    $status = 'multiple_purchase_lines_price_match';
                } else {
                    $priceDiscrepancyCount++;
                    $status = 'multiple_purchase_lines_price_discrepancy';
                }
                $processedRecords[] = [
                    'id' => $record->id,
                    'transaction_id' => $record->transaction_id,
                    'ref_no' => $record->ref_no,
                    'variation_id' => $record->variation_id,
                    'product_id' => $record->product_id,
                    'current_price' => $record->current_price,
                    'transfer_quantity' => $record->transfer_quantity,
                    'status' => $status,
                    'purchase_line_count' => $purchaseLineCount,
                    'found_purchase_line_ids' => $purchaseLines->pluck('purchase_line_id')->toArray(),
                    'purchase_line_prices' => $purchaseLines->pluck('purchase_price_inc_tax', 'purchase_line_id')->toArray(),
                    'correct_price' => $weightedPrice,
                    'current_total' => $currentTotal,
                    'correct_total' => $correctTotal,
                    'difference' => $difference
                ];
            } else {
                // Single purchase line found
                $purchaseLine = $purchaseLines->first();
                $singlePurchaseLineCount++;
                
                $currentTotal = $record->transfer_quantity * $record->current_price;
                $correctTotal = $record->transfer_quantity * $purchaseLine->purchase_price_inc_tax;
                $difference = $correctTotal - $currentTotal;
                
                $priceMatch = ($purchaseLine->purchase_price_inc_tax == $record->current_price);
                
                if ($priceMatch) {
                    $priceMatchCount++;
                    $status = 'price_match';
                } else {
                    $priceDiscrepancyCount++;
                    $status = 'price_discrepancy';
                }
                
                $processedRecords[] = [
                    'id' => $record->id,
                    'transaction_id' => $record->transaction_id,
                    'ref_no' => $record->ref_no,
                    'variation_id' => $record->variation_id,
                    'product_id' => $record->product_id,
                    'current_price' => $record->current_price,
                    'transfer_quantity' => $record->transfer_quantity,
                    'status' => $status,
                    'purchase_line_count' => 1,
                    'found_purchase_line_ids' => [$purchaseLine->purchase_line_id],
                    'correct_price' => $purchaseLine->purchase_price_inc_tax,
                    'current_total' => $currentTotal,
                    'correct_total' => $correctTotal,
                    'difference' => $difference
                ];
            }
        }

        return response()->json([
            'success' => true,
            'summary' => [
                'total_records' => $baseRecords->count(),
                'no_purchase_line' => $noPurchaseLineCount,
                'multiple_purchase_lines' => $multiplePurchaseLinesCount,
                'single_purchase_line' => $singlePurchaseLineCount,
                'price_match' => $priceMatchCount,
                'price_discrepancy' => $priceDiscrepancyCount,
                'will_be_fixed' => $priceDiscrepancyCount,
                'will_be_skipped' => $noPurchaseLineCount + $multiplePurchaseLinesCount + $priceMatchCount
            ],
            'records' => $processedRecords
        ]);
    }
    
    /**
     * Fix a specific record with null purchase_lines_id by ID
     */
    public function fixSpecificNullPurchaseLinesRecord($id)
    {
        // Initialize custom log file
        $this->initializeLogFile();
        
        try {
            DB::beginTransaction();

            // Get the specific record
            $record = DB::table('nst_prepare_products')
                ->select(
                    'nst_prepare_products.id',
                    'nst_prepare_products.transaction_id',
                    'nst_prepare_products.variation_id',
                    'nst_prepare_products.product_id',
                    'nst_prepare_products.unit_price_after_shipping as current_price',
                    'nst_prepare_products.transfer_quantity',
                    'nst_prepare_products.purchase_lines_id'
                )
                ->where('nst_prepare_products.id', $id)
                ->whereNull('nst_prepare_products.deleted_at')
                ->where('nst_prepare_products.status', 'complete')
                ->where('nst_prepare_products.transfer_quantity', '>' , 0)
                ->first();

            if (!$record) {
                $this->writeLog("Record not found or is deleted for nst_prepare_products.id: {$id}");
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found or is deleted',
                    'log_file' => $this->logFileName
                ], 404);
            }

            if ($record->purchase_lines_id !== null) {
                $this->writeLog("Record {$id} already has purchase_lines_id: {$record->purchase_lines_id}");
                return response()->json([
                    'success' => false,
                    'message' => "Record already has purchase_lines_id: {$record->purchase_lines_id}",
                    'log_file' => $this->logFileName
                ], 400);
            }

            $this->writeLog('========================================');
            $this->writeLog("SPECIFIC NULL PURCHASE_LINES_ID FIX");
            $this->writeLog("Record ID: {$id}");
            $this->writeLog('========================================');

            // Trace through relationships to find purchase_lines.id
            $purchaseLines = DB::table('transaction_sell_lines')
                ->join('transaction_sell_lines_purchase_lines', 
                    'transaction_sell_lines.id', '=', 'transaction_sell_lines_purchase_lines.sell_line_id')
                ->join('purchase_lines', 
                    'transaction_sell_lines_purchase_lines.purchase_line_id', '=', 'purchase_lines.id')
                ->select(
                    'purchase_lines.id as purchase_line_id',
                    'purchase_lines.purchase_price_inc_tax'
                )
                ->where('transaction_sell_lines.transaction_id', $record->transaction_id)
                ->where('transaction_sell_lines.variation_id', $record->variation_id)
                ->get();

            if ($purchaseLines->isEmpty()) {
                $this->writeLog("? Could not find purchase_line through relationships for record {$id}");
                DB::rollBack();
                return response()->json([
                    'success' => false,
                    'message' => 'Could not find purchase_line through relationships',
                    'log_file' => $this->logFileName
                ], 404);
            }

            // Check if there are multiple purchase lines
            if ($purchaseLines->count() > 1) {
                $this->writeLog("? Record {$id} has multiple purchase lines ({$purchaseLines->count()} found)");
                $this->writeLog("  Purchase Line IDs: " . $purchaseLines->pluck('purchase_line_id')->implode(', '));
                
                // Calculate weighted average
                $weightedPrice = $this->calculateWeightedAveragePrice(
                    $record->transaction_id,
                    $record->variation_id,
                    $record->transfer_quantity
                );

                if ($weightedPrice === null) {
                    $this->writeLog("  ? Could not calculate weighted average price");
                    DB::table('nst_prepare_products')
                        ->where('id', $id)
                        ->update([
                            'update_file_path' => 'many purchase lines error ' . $this->logFileName,
                            'updated_at' => now()
                        ]);
                    
                    DB::commit();
                    
                    return response()->json([
                        'success' => false,
                        'message' => 'Could not calculate weighted average price for multiple purchase lines',
                        'purchase_line_ids' => $purchaseLines->pluck('purchase_line_id')->toArray(),
                        'log_file' => $this->logFileName,
                        'log_path' => $this->logFilePath
                    ], 400);
                }

                $this->writeLog("  Calculated weighted average price: {$weightedPrice}");

                // Check if weighted price matches current price
                if (abs($weightedPrice - $record->current_price) < 0.01) {
                    $this->writeLog("  ? Weighted price matches current price, only updating purchase_lines_id");
                    
                    DB::table('nst_prepare_products')
                        ->where('id', $id)
                        ->update([
                            'purchase_lines_id' => $purchaseLines->first()->purchase_line_id,
                            'update_file_path' => 'multiple purchase lines weighted ' . $this->logFileName,
                            'updated_at' => now()
                        ]);
                    
                    DB::commit();
                    
                    return response()->json([
                        'success' => true,
                        'message' => "purchase_lines_id updated with weighted price matching - no pricing fix needed",
                        'purchase_line_ids' => $purchaseLines->pluck('purchase_line_id')->toArray(),
                        'weighted_price' => $weightedPrice,
                        'log_file' => $this->logFileName,
                        'log_path' => $this->logFilePath
                    ]);
                }

                // Fix with weighted average
                $this->fixMultiplePurchaseLinesRecord(
                    $record->id,
                    $record->transaction_id,
                    $record->variation_id,
                    $record->product_id,
                    $record->current_price,
                    $weightedPrice,
                    $record->transfer_quantity,
                    $purchaseLines
                );

                DB::commit();

                $this->writeLog('');
                $this->writeLog("? Successfully updated record ID: {$id} with weighted average");
                $this->writeLog("Completed at: " . now()->format('Y-m-d H:i:s'));
                $this->writeLog('========================================');

                return response()->json([
                    'success' => true,
                    'message' => "Successfully updated record ID: {$id} with weighted average price",
                    'record' => [
                        'id' => $id,
                        'purchase_line_ids' => $purchaseLines->pluck('purchase_line_id')->toArray(),
                        'old_price' => $record->current_price,
                        'new_weighted_price' => $weightedPrice
                    ],
                    'log_file' => $this->logFileName,
                    'log_path' => $this->logFilePath
                ]);
            }

            // Only one purchase line found - proceed with normal fix
            $purchaseLine = $purchaseLines->first();

            // Update purchase_lines_id in nst_prepare_products first
            DB::table('nst_prepare_products')
                ->where('id', $id)
                ->update([
                    'purchase_lines_id' => $purchaseLine->purchase_line_id,
                    'updated_at' => now()
                ]);

            $this->writeLog("? Updated purchase_lines_id to {$purchaseLine->purchase_line_id} for record {$id}");

            // Check if prices match - if they do, skip fixSingleRecord
            if ($purchaseLine->purchase_price_inc_tax == $record->current_price) {
                $this->writeLog("? Record {$id}: Prices already match ({$record->current_price})");
                $this->writeLog("No pricing fix needed - only purchase_lines_id was updated");
                
                DB::commit();
                
                return response()->json([
                    'success' => true,
                    'message' => "purchase_lines_id updated, but prices already match - no pricing fix needed",
                    'purchase_lines_id' => $purchaseLine->purchase_line_id,
                    'price' => $record->current_price,
                    'log_file' => $this->logFileName,
                    'log_path' => $this->logFilePath
                ]);
            }

            // Prices don't match, so call fixSingleRecord
            $recordToFix = (object) [
                'id' => $record->id,
                'transaction_id' => $record->transaction_id,
                'purchase_lines_id' => $purchaseLine->purchase_line_id,
                'variation_id' => $record->variation_id,
                'product_id' => $record->product_id,
                'old_price' => $record->current_price,
                'transfer_quantity' => $record->transfer_quantity,
                'correct_price' => $purchaseLine->purchase_price_inc_tax
            ];

            $this->fixSingleRecord($recordToFix);

            DB::commit();

            $this->writeLog('');
            $this->writeLog("? Successfully updated record ID: {$id}");
            $this->writeLog("Completed at: " . now()->format('Y-m-d H:i:s'));
            $this->writeLog('========================================');

            return response()->json([
                'success' => true,
                'message' => "Successfully updated record ID: {$id}",
                'record' => [
                    'id' => $id,
                    'purchase_lines_id' => $purchaseLine->purchase_line_id,
                    'old_price' => $record->current_price,
                    'new_price' => $purchaseLine->purchase_price_inc_tax
                ],
                'log_file' => $this->logFileName,
                'log_path' => $this->logFilePath
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            
            $this->writeLog("CRITICAL ERROR fixing specific null purchase_lines record {$id}: " . $e->getMessage());
            $this->writeLog("Stack trace: " . $e->getTraceAsString());
            
            return response()->json([
                'success' => false,
                'message' => 'Error updating record: ' . $e->getMessage(),
                'log_file' => $this->logFileName,
                'log_path' => $this->logFilePath
            ], 500);
        }
    }
    
    /**
     * Fix a specific record with multiple purchase lines by ID
     */
    public function fixSpecificMultiplePurchaseLinesRecord($id)
    {
        $this->initializeLogFile();
        
        try {
            DB::beginTransaction();

            $record = DB::table('nst_prepare_products')
                ->select(
                    'nst_prepare_products.id',
                    'nst_prepare_products.transaction_id',
                    'nst_prepare_products.variation_id',
                    'nst_prepare_products.product_id',
                    'nst_prepare_products.unit_price_after_shipping as current_price',
                    'nst_prepare_products.transfer_quantity'
                )
                ->where('nst_prepare_products.id', $id)
                ->whereNull('nst_prepare_products.deleted_at')
                ->where('nst_prepare_products.status', 'complete')
                ->where('nst_prepare_products.transfer_quantity', '>' , 0)
                ->first();

            if (!$record) {
                $this->writeLog("Record not found or is deleted for nst_prepare_products.id: {$id}");
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found or is deleted',
                    'log_file' => $this->logFileName
                ], 404);
            }

            $this->writeLog('========================================');
            $this->writeLog("SPECIFIC MULTIPLE PURCHASE LINES FIX");
            $this->writeLog("Record ID: {$id}");
            $this->writeLog('========================================');

            $purchaseLines = DB::table('transaction_sell_lines')
                ->join('transaction_sell_lines_purchase_lines', 
                    'transaction_sell_lines.id', '=', 'transaction_sell_lines_purchase_lines.sell_line_id')
                ->join('purchase_lines', 
                    'transaction_sell_lines_purchase_lines.purchase_line_id', '=', 'purchase_lines.id')
                ->select(
                    'purchase_lines.id as purchase_line_id',
                    'purchase_lines.purchase_price_inc_tax'
                )
                ->where('transaction_sell_lines.transaction_id', $record->transaction_id)
                ->where('transaction_sell_lines.variation_id', $record->variation_id)
                ->get();

            if ($purchaseLines->isEmpty()) {
                $this->writeLog("? Could not find purchase lines for record {$id}");
                DB::rollBack();
                return response()->json([
                    'success' => false,
                    'message' => 'Could not find purchase lines',
                    'log_file' => $this->logFileName
                ], 404);
            }

            if ($purchaseLines->count() == 1) {
                $this->writeLog("? Record {$id} has only one purchase line - use fixSpecificNullPurchaseLinesRecord instead");
                DB::rollBack();
                return response()->json([
                    'success' => false,
                    'message' => 'Record has only one purchase line - not a multiple purchase lines case',
                    'log_file' => $this->logFileName
                ], 400);
            }

            $this->writeLog("Found {$purchaseLines->count()} purchase lines");
            $this->writeLog("Purchase Line IDs: " . $purchaseLines->pluck('purchase_line_id')->implode(', '));

            // Calculate weighted average price
            $weightedPrice = $this->calculateWeightedAveragePrice(
                $record->transaction_id,
                $record->variation_id,
                $record->transfer_quantity
            );

            if ($weightedPrice === null) {
                $this->writeLog("? Could not calculate weighted average price");
                DB::rollBack();
                return response()->json([
                    'success' => false,
                    'message' => 'Could not calculate weighted average price',
                    'log_file' => $this->logFileName
                ], 500);
            }

            $this->writeLog("Calculated weighted average price: {$weightedPrice}");

            // Fix the record
            $this->fixMultiplePurchaseLinesRecord(
                $record->id,
                $record->transaction_id,
                $record->variation_id,
                $record->product_id,
                $record->current_price,
                $weightedPrice,
                $record->transfer_quantity,
                $purchaseLines
            );

            DB::commit();

            $this->writeLog('');
            $this->writeLog("? Successfully updated record ID: {$id}");
            $this->writeLog("Completed at: " . now()->format('Y-m-d H:i:s'));
            $this->writeLog('========================================');

            return response()->json([
                'success' => true,
                'message' => "Successfully updated record ID: {$id} with weighted average price",
                'record' => [
                    'id' => $id,
                    'purchase_line_ids' => $purchaseLines->pluck('purchase_line_id')->toArray(),
                    'old_price' => $record->current_price,
                    'new_weighted_price' => $weightedPrice
                ],
                'log_file' => $this->logFileName,
                'log_path' => $this->logFilePath
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            
            $this->writeLog("CRITICAL ERROR fixing multiple purchase lines record {$id}: " . $e->getMessage());
            $this->writeLog("Stack trace: " . $e->getTraceAsString());
            
            return response()->json([
                'success' => false,
                'message' => 'Error updating record: ' . $e->getMessage(),
                'log_file' => $this->logFileName,
                'log_path' => $this->logFilePath
            ], 500);
        }
    }
    
    /**
     * Calculate weighted average price for multiple purchase lines
     */
//     private function calculateWeightedAveragePrice($transactionId, $variationId, $transferQuantity)
//     {
//         try {
//             $purchaseLineDetails = DB::table('transaction_sell_lines')
//                 ->join('transaction_sell_lines_purchase_lines', 
//                     'transaction_sell_lines.id', '=', 'transaction_sell_lines_purchase_lines.sell_line_id')
//                 ->join('purchase_lines', 
//                     'transaction_sell_lines_purchase_lines.purchase_line_id', '=', 'purchase_lines.id')
//                 ->select(
//                     'transaction_sell_lines.id as transaction_sell_line_id',
//                     'purchase_lines.id as purchase_line_id',
//                     'purchase_lines.purchase_price_inc_tax',
//                     'transaction_sell_lines_purchase_lines.quantity',
//                     'transaction_sell_lines_purchase_lines.qty_returned'
//                 )
//                 ->where('transaction_sell_lines.transaction_id', $transactionId)
//                 ->where('transaction_sell_lines.variation_id', $variationId)
//                 ->get();
// dd($purchaseLineDetails , $transferQuantity);
//             if ($purchaseLineDetails->isEmpty()) {
//                 return null;
//             }

//             $totalWeightedPrice = 0;

//             foreach ($purchaseLineDetails as $detail) {
//                 $effectiveQuantity = $detail->quantity - ($detail->qty_returned ?? 0);
//                 $lineTotal = $effectiveQuantity * $detail->purchase_price_inc_tax;
//                 $totalWeightedPrice += $lineTotal;
//             }

//             // Calculate weighted average: sum of (qty * price) / transfer_quantity
//             $weightedAverage = $totalWeightedPrice / $transferQuantity;

//             return round($weightedAverage, 4);

//         } catch (\Exception $e) {
//             $this->writeLog("Error calculating weighted average: " . $e->getMessage());
//             return null;
//         }
//     }

private function calculateWeightedAveragePrice($transactionId, $variationId, $transferQuantity)
{
    try {
        // Group by purchase_line_id to avoid counting duplicates
        $purchaseLineDetails = DB::table('transaction_sell_lines')
            ->join('transaction_sell_lines_purchase_lines', 
                'transaction_sell_lines.id', '=', 'transaction_sell_lines_purchase_lines.sell_line_id')
            ->join('purchase_lines', 
                'transaction_sell_lines_purchase_lines.purchase_line_id', '=', 'purchase_lines.id')
            ->select(
                'purchase_lines.id as purchase_line_id',
                'purchase_lines.purchase_price_inc_tax',
                DB::raw('SUM(transaction_sell_lines_purchase_lines.quantity) as total_quantity'),
                DB::raw('SUM(transaction_sell_lines_purchase_lines.qty_returned) as total_qty_returned')
            )
            ->where('transaction_sell_lines.transaction_id', $transactionId)
            ->where('transaction_sell_lines.variation_id', $variationId)
            ->groupBy('purchase_lines.id', 'purchase_lines.purchase_price_inc_tax')
            ->get();

        if ($purchaseLineDetails->isEmpty()) {
            return null;
        }

        $totalWeightedPrice = 0;
        $totalEffectiveQuantity = 0;

        foreach ($purchaseLineDetails as $detail) {
            $effectiveQuantity = $detail->total_quantity - ($detail->total_qty_returned ?? 0);
            $lineTotal = $effectiveQuantity * $detail->purchase_price_inc_tax;
            $totalWeightedPrice += $lineTotal;
            $totalEffectiveQuantity += $effectiveQuantity;
        }

        // Validate that effective quantity matches transfer quantity
        if (abs($totalEffectiveQuantity - $transferQuantity) > 0.01) {
            $this->writeLog("  ? Warning: Total effective quantity ({$totalEffectiveQuantity}) doesn't match transfer quantity ({$transferQuantity})");
        }

        // Calculate weighted average: sum of (qty * price) / total_quantity
        $weightedAverage = $totalWeightedPrice / $totalEffectiveQuantity;
dd($totalWeightedPrice , $totalEffectiveQuantity , $weightedAverage);
        return round($weightedAverage, 4);

    } catch (\Exception $e) {
        $this->writeLog("Error calculating weighted average: " . $e->getMessage());
        return null;
    }
}
    
    /**
     * Fix a single record with multiple purchase lines using weighted average price
     */
    private function fixMultiplePurchaseLinesRecord($recordId, $transactionId, $variationId, $productId, $oldPrice, $correctPrice, $transferQuantity, $purchaseLines)
    {
        $this->writeLog('');
        $this->writeLog('--------------------------------------------------------------');
        $this->writeLog("FIXING MULTIPLE PURCHASE LINES FOR nst_prepare_products.id: {$recordId}");
        $this->writeLog('--------------------------------------------------------------');
        $this->writeLog("Transaction ID: {$transactionId}");
        $this->writeLog("Variation ID: {$variationId}");
        $this->writeLog("Product ID: {$productId}");
        $this->writeLog("Multiple Purchase Lines: " . $purchaseLines->pluck('purchase_line_id')->implode(', '));
        $this->writeLog("Old Price: {$oldPrice} ? New Weighted Price: {$correctPrice}");
        $this->writeLog("Transfer Quantity: {$transferQuantity}");

        $oldTotal = $transferQuantity * $oldPrice;
        $newTotal = $transferQuantity * $correctPrice;
        $difference = $newTotal - $oldTotal;
        $this->writeLog("Price Difference: {$difference} (Old Total: {$oldTotal} ? New Total: {$newTotal})");
        
        // Show calculation breakdown
        $this->writeLog("Weighted Average Calculation:");
        $purchaseLineDetails = DB::table('transaction_sell_lines')
            ->join('transaction_sell_lines_purchase_lines', 
                'transaction_sell_lines.id', '=', 'transaction_sell_lines_purchase_lines.sell_line_id')
            ->join('purchase_lines', 
                'transaction_sell_lines_purchase_lines.purchase_line_id', '=', 'purchase_lines.id')
            ->select(
                'purchase_lines.id as purchase_line_id',
                'purchase_lines.purchase_price_inc_tax',
                DB::raw('SUM(transaction_sell_lines_purchase_lines.quantity) as total_quantity'),
                DB::raw('SUM(transaction_sell_lines_purchase_lines.qty_returned) as total_qty_returned')
            )
            ->where('transaction_sell_lines.transaction_id', $transactionId)
            ->where('transaction_sell_lines.variation_id', $variationId)
            ->groupBy('purchase_lines.id', 'purchase_lines.purchase_price_inc_tax')
            ->get();

        $totalWeightedPrice = 0;
        $totalEffectiveQty = 0;
        // foreach ($purchaseLineDetails as $detail) {
        //     $effectiveQty = $detail->quantity - ($detail->qty_returned ?? 0);
        //     $lineTotal = $effectiveQty * $detail->purchase_price_inc_tax;
        //     $totalWeightedPrice += $lineTotal;
        //     $this->writeLog("  PL {$detail->purchase_line_id}: ({$detail->quantity} - {$detail->qty_returned}) � {$detail->purchase_price_inc_tax} = {$lineTotal}");
        // }
        foreach ($purchaseLineDetails as $detail) {
            $effectiveQty = $detail->total_quantity - ($detail->total_qty_returned ?? 0);
            $lineTotal = $effectiveQty * $detail->purchase_price_inc_tax;
            $totalWeightedPrice += $lineTotal;
            $totalEffectiveQty += $effectiveQty;
            $this->writeLog("  PL {$detail->purchase_line_id}: ({$detail->total_quantity} - {$detail->total_qty_returned}) � {$detail->purchase_price_inc_tax} = {$lineTotal}");
        }
        $this->writeLog("  Total: {$totalWeightedPrice} � {$transferQuantity} = {$correctPrice}");
        
        $this->writeLog('--------------------------------------------------------------');

        // 1. Update transaction_sell_lines
        $this->writeLog('[1] TRANSACTION_SELL_LINES:');
        $sellLinesBefore = DB::table('transaction_sell_lines')
            ->where('transaction_id', $transactionId)
            ->where('product_id', $productId)
            ->where('variation_id', $variationId)
            ->get();

        if ($sellLinesBefore->isNotEmpty()) {
            foreach ($sellLinesBefore as $line) {
                $this->writeLog("  BEFORE (ID: {$line->id}): {$line->unit_price_inc_tax}");
            }

            DB::table('transaction_sell_lines')
                ->where('transaction_id', $transactionId)
                ->where('product_id', $productId)
                ->where('variation_id', $variationId)
                ->update([
                    'unit_price_before_discount' => $correctPrice,
                    'unit_price' => $correctPrice,
                    'unit_price_inc_tax' => $correctPrice,
                    'updated_at' => now()
                ]);

            $this->writeLog("  AFTER: {$correctPrice}");
            $this->writeLog("  ? Updated " . $sellLinesBefore->count() . " record(s)");
        }
        $this->writeLog('--------------------------------------------------------------');

        // 2. Update purchase_lines (child transaction)
        $this->writeLog('[2] PURCHASE_LINES (Child Transaction):');
        $purchaseTransaction = DB::table('transactions')
            ->where('transfer_parent_id', $transactionId)
            ->select('id')
            ->first();

        if ($purchaseTransaction) {
            $purchaseTransactionId = $purchaseTransaction->id;
            $this->writeLog("  Child Transaction ID: {$purchaseTransactionId}");

            $purchaseLinesBefore = DB::table('purchase_lines')
                ->where('transaction_id', $purchaseTransactionId)
                ->where('product_id', $productId)
                ->where('variation_id', $variationId)
                ->get();

            if ($purchaseLinesBefore->isNotEmpty()) {
                foreach ($purchaseLinesBefore as $line) {
                    $this->writeLog("  BEFORE (ID: {$line->id}): {$line->purchase_price_inc_tax}");
                }

                DB::table('purchase_lines')
                    ->where('transaction_id', $purchaseTransactionId)
                    ->where('product_id', $productId)
                    ->where('variation_id', $variationId)
                    ->update([
                        'pp_without_discount' => $correctPrice,
                        'purchase_price' => $correctPrice,
                        'purchase_price_inc_tax' => $correctPrice,
                        'updated_at' => now()
                    ]);

                $this->writeLog("  AFTER: {$correctPrice}");
                $this->writeLog("  ? Updated " . $purchaseLinesBefore->count() . " record(s)");
            }
        }
        $this->writeLog('--------------------------------------------------------------');

        // 3. Update nst_prepare_products
        $this->writeLog('[3] NST_PREPARE_PRODUCTS:');
        $this->writeLog("  BEFORE: {$oldPrice}");

        DB::table('nst_prepare_products')
            ->where('id', $recordId)
            ->update([
                'unit_price_after_shipping' => $correctPrice,
                // 'purchase_lines_id' => $purchaseLines->first()->purchase_line_id,
                'update_file_path' => 'multiple purchase lines weighted ' . $this->logFileName,
                'updated_at' => now()
            ]);

        $this->writeLog("  AFTER: {$correctPrice}");
        $this->writeLog("  ? Updated (set purchase_lines_id to {$purchaseLines->first()->purchase_line_id})");
        $this->writeLog('--------------------------------------------------------------');

        // 4. Update transactions (main transaction)
        $this->writeLog('[4] TRANSACTIONS (Main Transaction):');
        $mainTransactionBefore = DB::table('transactions')
            ->where('id', $transactionId)
            ->select('id', 'final_total')
            ->first();

        if ($mainTransactionBefore) {
            $this->writeLog("  BEFORE: {$mainTransactionBefore->final_total}");
            
            DB::table('transactions')
                ->where('id', $transactionId)
                ->update([
                    'final_total' => DB::raw("final_total + ({$difference})"),
                    'updated_at' => now()
                ]);

            $newFinalTotal = $mainTransactionBefore->final_total + $difference;
            $this->writeLog("  AFTER: {$newFinalTotal}");
            $this->writeLog("  ? Updated");
        }
        $this->writeLog('--------------------------------------------------------------');

        // 5. Update child transactions
        $this->writeLog('[5] TRANSACTIONS (Child Transactions):');
        $childTransactionsBefore = DB::table('transactions')
            ->where('transfer_parent_id', $transactionId)
            ->select('id', 'final_total')
            ->get();

        if ($childTransactionsBefore->isNotEmpty()) {
            foreach ($childTransactionsBefore as $childTrans) {
                $this->writeLog("  BEFORE (ID: {$childTrans->id}): {$childTrans->final_total}");
            }

            DB::table('transactions')
                ->where('transfer_parent_id', $transactionId)
                ->update([
                    'final_total' => DB::raw("final_total + ({$difference})"),
                    'updated_at' => now()
                ]);

            $this->writeLog("  ? Updated " . $childTransactionsBefore->count() . " child transaction(s)");
        }

        $this->writeLog('--------------------------------------------------------------');
        $this->writeLog("? COMPLETED FIX FOR MULTIPLE PURCHASE LINES: {$recordId}");
        $this->writeLog('--------------------------------------------------------------');
        $this->writeLog('');
    }
}