<?php

namespace App\Http\Controllers\Warehouse;

use App\Http\Controllers\Controller;
use App\Models\Department;
use App\Models\User;
use App\Models\Warehouse\Project;
use App\Models\Warehouse\ProjectDivision;
use App\Models\Warehouse\IncomingOperation;
use App\Models\Warehouse\IncomingOperationItem;
use App\Models\Warehouse\Item;
use App\Models\Warehouse\LpoManagement;
use App\Models\Warehouse\OutgoingTransaction;
use App\Models\Warehouse\QualityInspectionChecklist;
use App\Models\Warehouse\Supplier;
use App\Models\Warehouse\StockMovement;
use App\Models\Warehouse\IndirectGoods\IndirectGood;
use App\Models\Warehouse\IndirectGoods\IndirectGoodsIncoming;
use App\Models\Warehouse\IndirectGoods\IndirectGoodsProjectInventory;
use App\Models\Warehouse\IndirectGoods\IndirectGoodsStockMovement;
use App\Models\DamagedItem;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Gate;
use Illuminate\Support\Facades\Storage;
use Illuminate\Validation\Rule;

/**
 * Incoming Operations Controller
 *
 * SITE RETURN LOGIC DOCUMENTATION:
 * ================================
 *
 * Site returns support 4 reference types:
 *
 * 1. MATERIAL REQUEST (MR) Returns:
 *    - Reference: material_request_id + outgoing_item_id
 *    - Project determined from: original material request's project_id
 *    - Flow: processItemToInventory() → updateInventory() + updateProjectInventory() → subtractFromProjectInventory()
 *
 * 2. DIRECT DELIVERY (DD) Returns:
 *    - Reference: direct_delivery_id + direct_delivery_item_id
 *    - Project determined from: site return operation's project_id (direct delivery to project)
 *    - Flow: Same as MR returns
 *
 * 3. MATERIAL TRANSFER (MT) Returns:
 *    - Reference: material_transfer_id + material_transfer_item_id
 *    - Project determined from: original material transfer's receiver_project_id
 *    - Flow: Same as MR returns
 *
 * 4. SUPPLIER DELIVERY (SD) Returns:
 *    - Reference: supplier_delivery_id + supplier_delivery_item_id
 *    - Project determined from: site return operation's project_id (supplier delivery to project)
 *    - Flow: Same as all other returns - uses standard processItemToInventory()
 *
 * ALL RETURN TYPES use the same standard flow:
 * - Add returned quantity to warehouse inventory (updateInventory)
 * - Subtract returned quantity from project inventory (subtractFromProjectInventory)
 * - Create stock movements and logs for tracking
 *
 * Return quantity calculation for display:
 * - Previously returned = sum of IncomingOperationItems where reference_item_id matches
 * - Available to return = originally_delivered - previously_returned
 */
class IncomingOperationController extends Controller
{
    /**
     * Display a listing of incoming operations
     */
    public function index(Request $request)
    {
        $query = IncomingOperation::with(['supplier', 'project', 'division', 'receiver', 'inspector', 'materialRequest'])
                                 ->latest('operation_date');

        // Apply filters
        if ($request->filled('type')) {
            $query->where('operation_type', $request->type);
        } elseif ($request->filled('operation_type')) {
            $query->where('operation_type', $request->operation_type);
        }

        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('project_id')) {
            $query->where('project_id', $request->project_id);
        }

        if ($request->filled('supplier_id')) {
            $query->where('supplier_id', $request->supplier_id);
        }

        if ($request->filled('division_id')) {
            $query->where('division_id', $request->division_id);
        }

        if ($request->filled('date_from')) {
            $query->where('operation_date', '>=', $request->date_from);
        }

        if ($request->filled('date_to')) {
            $query->where('operation_date', '<=', $request->date_to);
        }

        if ($request->filled('mr_reference')) {
            $query->whereHas('materialRequest', function($q) use ($request) {
                $q->where('material_request_number', 'like', '%' . $request->mr_reference . '%');
            });
        }

        // Search filter
        if ($request->filled('search')) {
            $search = $request->search;
            $query->where(function($q) use ($search) {
                $q->where('operation_number', 'like', "%{$search}%")
                  ->orWhereHas('supplier', function($sq) use ($search) {
                      $sq->where('name', 'like', "%{$search}%");
                  });
            });
        }

        $operations = $query->paginate(25)->withQueryString();

        // Get statistics for the cards
        $statistics = [
            'direct_processing' => IncomingOperation::whereIn('operation_type', ['supplier_delivery', 'site_return'])->count(),
            'pending_review' => IncomingOperation::whereIn('status', ['pending_inspection', 'approved'])->count(),
            'completed' => IncomingOperation::where('status', 'completed')->count(),
        ];

        // Get filter options
        $suppliers = Supplier::where('status', 'active')->get();
        $projects = Project::all();
        $divisions = Department::where('is_active', true)->get();

        return view('warehouse.incoming-operations.index', compact(
            'operations', 'suppliers', 'projects', 'divisions', 'statistics'
        ));
    }

    /**
     * Show the form for creating a new incoming operation
     */
    public function create(Request $request)
    {
        $operationType = $request->get('type', 'supplier_delivery');

        $suppliers = Supplier::where('status', 'active')->get();
        $projects = Project::all();
        $divisions = Department::where('is_active', true)->get();
        $inspectors = User::whereHas('role', function ($query) {
            $query->whereIn('name', ['warehouse_manager', 'warehouse_supervisor']);
        })->get();
        $lpos = LpoManagement::where('status', 'approved')->get();

        // Debug: Log the counts for troubleshooting
        \Log::info('Incoming Operations Create Data Loaded:', [
            'suppliers_count' => $suppliers->count(),
            'projects_count' => $projects->count(),
            'divisions_count' => $divisions->count(),
            'inspectors_count' => $inspectors->count(),
            'lpos_count' => $lpos->count(),
            'operation_type' => $operationType
        ]);

        // Get currency exchange rates (this could be from a service)
        $exchangeRates = [
            'AED' => 1.0000,
            'USD' => 3.6725, // Example rate
            'EUR' => 4.0250  // Example rate
        ];

        return view('warehouse.incoming-operations.create', compact(
            'operationType', 'suppliers', 'projects', 'divisions',
            'inspectors', 'lpos', 'exchangeRates'
        ));
    }

    /**
     * Store a newly created incoming operation
     */
    public function store(Request $request)
    {
        // Debug logging
        \Log::info('Supplier Delivery Store Method Called', [
            'all_data' => $request->all(),
            'operation_type' => $request->operation_type,
            'items_count' => count($request->items ?? []),
            'method' => $request->method()
        ]);

        // Set default values for currency and exchange_rate if not provided
        if (!$request->has('currency') || empty($request->currency)) {
            $request->merge(['currency' => 'AED']);
        }
        if (!$request->has('exchange_rate') || empty($request->exchange_rate)) {
            $request->merge(['exchange_rate' => 1.0]);
        }

        if ($request->has('mixed_items')) {
            $directItems = [];
            $indirectItems = [];

            foreach ($request->input('mixed_items', []) as $index => $itemData) {
                $itemKey = $itemData['item_key'] ?? '';
                if (!str_contains($itemKey, ':')) {
                    continue;
                }

                [$goodsType, $itemId] = explode(':', $itemKey, 2);

                if ($goodsType === 'direct') {
                    $directItems[$index] = [
                        'item_id' => $itemId,
                        'quantity_delivered' => $itemData['quantity_delivered'] ?? null,
                        'unit_price' => $itemData['unit_price'] ?? null,
                        'batch_number' => $itemData['batch_number'] ?? null,
                        'production_date' => $itemData['production_date'] ?? null,
                        'expiry_date' => $itemData['expiry_date'] ?? null,
                    ];
                } elseif ($goodsType === 'indirect') {
                    $indirectItems[$index] = [
                        'indirect_good_id' => $itemId,
                        'quantity_delivered' => $itemData['quantity_delivered'] ?? null,
                        'unit_price' => $itemData['unit_price'] ?? null,
                        'batch_number' => $itemData['batch_number'] ?? null,
                        'expiry_date' => $itemData['expiry_date'] ?? null,
                    ];
                }
            }

            $request->merge([
                'items' => $directItems,
                'indirect_items' => $indirectItems,
            ]);
        }

        // Different validation rules for different operation types
        $validationRules = [
            'operation_type' => 'required|in:supplier_delivery,site_return,emergency_procurement',
            'operation_date' => 'required|date',
            'received_by' => 'nullable|exists:users,id',

            // Files
            'delivery_note_file' => 'nullable|file|mimes:pdf,jpg,jpeg,png|max:10240',
            'photos.*' => 'nullable|file|mimes:jpg,jpeg,png|max:5120'
        ];

        // Site return specific validation
        if ($request->operation_type === 'site_return') {
            // Check if this is a multi-reference "all items" or "consolidated" (FIFO allocation) return
            $isAllItemsMode = $request->reference_type === 'all_items';
            $isConsolidatedMode = $request->reference_type === 'consolidated';

            $validationRules = array_merge($validationRules, [
                'project_id' => 'required|exists:projects,id',
                'division' => 'nullable|string|max:255',
                'delivery_file' => 'nullable|file|max:10240|mimes:pdf,doc,docx,jpg,jpeg,png',
                'currency' => 'required|in:AED,USD,EUR',
                'exchange_rate' => 'required|numeric|min:0.0001',

                // Items for site return
                'items' => 'required|array|min:1',
                'items.*.item_id' => 'required|exists:items,id',
                'items.*.outgoing_item_id' => 'nullable|exists:outgoing_items,id',
                'items.*.direct_delivery_item_id' => 'nullable|exists:direct_delivery_items,id',
                'items.*.material_transfer_item_id' => 'nullable|exists:material_transfer_request_items,id',
                'items.*.supplier_delivery_item_id' => 'nullable|exists:incoming_operation_items,id',
                'items.*.outgoing_fulfillment_id' => 'nullable|exists:outgoing_fulfillments,id',
                'items.*.quantity_returned' => 'nullable|numeric|min:0',
                'items.*.returnable_qty' => 'nullable|numeric|min:0',
                'items.*.damaged_qty' => 'nullable|numeric|min:0',
                'items.*.condition' => 'nullable|string|in:good,fair,damaged',
                'items.*.return_reason' => 'nullable|string',
                'items.*.notes' => 'nullable|string',

                // Multi-reference item fields (for all_items mode)
                'items.*.reference_type' => 'nullable|string',
                'items.*.reference_id' => 'nullable|integer',
                'items.*.reference_item_id' => 'nullable|integer',

                // Consolidated mode (FIFO allocation) fields
                'items.*.allocations' => 'nullable|string', // JSON string of allocations

                // Force close validation
                'force_close_return' => 'nullable|boolean',
                'force_close_reason' => 'nullable|required_if:force_close_return,1|string'
            ]);

            // Only require single reference if not in all_items mode or consolidated mode
            if (!$isAllItemsMode && !$isConsolidatedMode) {
                $validationRules['material_request_id'] = 'required_without_all:direct_delivery_id,material_transfer_id,supplier_delivery_id|exists:outgoing_transactions,id';
                $validationRules['direct_delivery_id'] = 'required_without_all:material_request_id,material_transfer_id,supplier_delivery_id|exists:direct_deliveries,id';
                $validationRules['material_transfer_id'] = 'required_without_all:material_request_id,direct_delivery_id,supplier_delivery_id|exists:material_transfer_requests,id';
                $validationRules['supplier_delivery_id'] = 'required_without_all:material_request_id,direct_delivery_id,material_transfer_id|exists:incoming_operations,id';
            }
        } else {
            // Standard operation validation (supplier delivery, etc.)
            $validationRules = array_merge($validationRules, [
                'division' => 'required_if:operation_type,supplier_delivery|exists:project_divisions,id',
                'supplier_id' => 'required_if:operation_type,supplier_delivery|exists:suppliers,id',
                'lpo_number' => 'nullable|string',
                'delivery_note_number' => 'nullable|string',
                'delivery_file' => 'nullable|file|max:10240|mimes:pdf,doc,docx,jpg,jpeg,png',
                'currency' => 'required|in:AED,USD,EUR',
                'exchange_rate' => 'required|numeric|min:0.0001',

                // Delivery type validation
                'delivery_type' => 'required|in:warehouse,project_site,foc',
                'project_id' => 'required_if:delivery_type,project_site|nullable|exists:projects,id',

                // Items for standard operations
                'items' => 'nullable|array',
                'items.*.item_id' => 'nullable|exists:items,id',
                'items.*.quantity_delivered' => 'nullable|numeric|min:0.01',
                'items.*.unit_price' => 'nullable|numeric|min:0',
                'items.*.batch_number' => 'nullable|string',
                'items.*.production_date' => 'nullable|date',
                'items.*.expiry_date' => 'nullable|date',

                // Indirect goods for supplier delivery
                'indirect_items' => 'nullable|array',
                'indirect_items.*.indirect_good_id' => 'nullable|exists:indirect_goods,id',
                'indirect_items.*.quantity_delivered' => 'nullable|numeric|min:0.01',
                'indirect_items.*.unit_price' => 'nullable|numeric|min:0',
                'indirect_items.*.batch_number' => 'nullable|string',
                'indirect_items.*.expiry_date' => 'nullable|date'
            ]);
        }

        try {
            $request->validate($validationRules);
            \Log::info('Validation passed for operation type: ' . $request->operation_type);
        } catch (\Illuminate\Validation\ValidationException $e) {
            \Log::error('Validation failed for supplier delivery', [
                'errors' => $e->errors(),
                'request_data' => $request->all()
            ]);
            throw $e;
        }

        // Debug logging for site returns
        if ($request->operation_type === 'site_return') {
            \Log::info('Site Return Submission Debug', [
                'project_id' => $request->project_id,
                'material_request_id' => $request->material_request_id,
                'direct_delivery_id' => $request->direct_delivery_id,
                'material_transfer_id' => $request->material_transfer_id,
                'items_count' => count($request->items ?? []),
                'all_request_data' => $request->all()
            ]);
        }

        // Additional validation for site returns to ensure data integrity
        if ($request->operation_type === 'site_return') {
            $isAllItemsMode = $request->reference_type === 'all_items';
            $isConsolidatedMode = $request->reference_type === 'consolidated';

            // For all_items mode or consolidated mode, each item has its own reference(s) - no need for single reference validation
            if (!$isAllItemsMode && !$isConsolidatedMode) {
                if (empty($request->material_request_id) && empty($request->direct_delivery_id) && empty($request->material_transfer_id) && empty($request->supplier_delivery_id)) {
                    return back()->withErrors(['reference' => 'Either Material Request, Direct Delivery, Material Transfer, or Supplier Delivery must be selected.']);
                }
            }

            if (empty($request->project_id)) {
                return back()->withErrors(['project_id' => 'Project must be selected for site returns.']);
            }

            if (empty($request->items) || count($request->items) == 0) {
                return back()->withErrors(['items' => 'At least one item must be returned.']);
            }

            // For all_items mode, validate that each item has proper reference info
            if ($isAllItemsMode) {
                foreach ($request->items as $index => $item) {
                    if (empty($item['reference_type']) || empty($item['reference_id'])) {
                        return back()->withErrors(['items' => "Item at position {$index} is missing reference information."]);
                    }
                }
            }

            // For consolidated mode, validate that each item has allocations
            if ($isConsolidatedMode) {
                foreach ($request->items as $index => $item) {
                    if (empty($item['allocations'])) {
                        return back()->withErrors(['items' => "Item at position {$index} is missing allocation information."]);
                    }
                }
            }
        }

        // Additional validation for supplier delivery: unit price must be > 0 for warehouse and project_site deliveries
        if ($request->operation_type === 'supplier_delivery') {
            $deliveryType = $request->delivery_type;
            $directItems = collect($request->items ?? [])->filter(function ($item) {
                return !empty($item['item_id']) && floatval($item['quantity_delivered'] ?? 0) > 0;
            });
            $indirectItems = collect($request->indirect_items ?? [])->filter(function ($item) {
                return !empty($item['indirect_good_id']) && floatval($item['quantity_delivered'] ?? 0) > 0;
            });

            if ($directItems->isEmpty() && $indirectItems->isEmpty()) {
                return back()->withErrors([
                    'items' => 'Please add at least one direct or indirect goods item to the delivery.'
                ])->withInput();
            }

            // For warehouse and project_site deliveries, unit price must be greater than 0
            if (in_array($deliveryType, ['warehouse', 'project_site'])) {
                foreach ($directItems as $index => $item) {
                    $unitPrice = floatval($item['unit_price'] ?? 0);
                    if ($unitPrice <= 0) {
                        $itemModel = Item::find($item['item_id']);
                        $itemName = $itemModel ? $itemModel->item_code . ' - ' . $itemModel->item_description : "Item #{$index}";
                        return back()->withErrors([
                            'items' => "Unit price must be greater than 0 for warehouse and project site deliveries. Please enter a valid unit price for: {$itemName}"
                        ])->withInput();
                    }
                }

                foreach ($indirectItems as $index => $item) {
                    $unitPrice = floatval($item['unit_price'] ?? 0);
                    if ($unitPrice <= 0) {
                        $itemModel = IndirectGood::find($item['indirect_good_id']);
                        $itemName = $itemModel ? $itemModel->ig_code . ' - ' . $itemModel->ig_description : "Indirect item #{$index}";
                        return back()->withErrors([
                            'indirect_items' => "Unit price must be greater than 0 for warehouse and project site deliveries. Please enter a valid unit price for: {$itemName}"
                        ])->withInput();
                    }
                }
            }
        }

        DB::beginTransaction();

        try {
            // Create the operation with retry mechanism for duplicate operation numbers
            $maxRetries = 3;
            $retryCount = 0;
            $operation = null;

            // Determine division_id - for site returns, get from items or material request
            $divisionId = null;
            if ($request->operation_type === 'site_return') {
                // For site returns, try to get division from the material request
                if ($request->material_request_id) {
                    $materialRequest = OutgoingTransaction::with('items.division')->find($request->material_request_id);
                    if ($materialRequest) {
                        // Try transaction-level division first
                        if ($materialRequest->division_id) {
                            $divisionId = $materialRequest->division_id;
                        } else {
                            // Get first item's division
                            $firstItemWithDivision = $materialRequest->items->first(fn($item) => $item->division_id);
                            if ($firstItemWithDivision) {
                                $divisionId = $firstItemWithDivision->division_id;
                            }
                        }
                    }
                }
            } else {
                // For other operations, use the division from request if it's numeric
                $divisionId = is_numeric($request->division) ? $request->division : null;
            }

            while ($retryCount < $maxRetries && !$operation) {
                try {
                    $operation = IncomingOperation::create([
                'operation_type' => $request->operation_type,
                'operation_date' => $request->operation_date,
                'status' => 'draft',
                'supplier_id' => $request->supplier_id,
                'material_request_id' => $request->material_request_id,
                'direct_delivery_id' => $request->direct_delivery_id,
                'material_transfer_id' => $request->material_transfer_id,
                'supplier_delivery_id' => $request->supplier_delivery_id,
                'project_id' => ($request->operation_type === 'site_return' || $request->delivery_type === 'project_site') ? $request->project_id : null,
                'division' => $divisionId,
                'division_id' => $divisionId,
                'lpo_number' => $request->lpo_number,
                'currency' => $request->currency,
                'exchange_rate' => $request->exchange_rate,
                'delivery_note_number' => $request->delivery_note_number,
                'force_close_return' => $request->boolean('force_close_return', false),
                'force_close_reason' => $request->force_close_reason,
                'delivery_type' => $request->delivery_type ?? 'warehouse',
                'delivery_address' => $request->delivery_address,
                'inspector_id' => $request->inspector_id,
                'vat_rate' => 5.00, // UAE VAT rate
                'total_amount' => 0, // Initial value, will be updated after items are added
                'received_by' => $request->received_by,
                'received_by_name' => $request->received_by_name,
                'notes' => $request->notes
            ]);
                } catch (\Illuminate\Database\QueryException $e) {
                    if (str_contains($e->getMessage(), 'Duplicate entry') && str_contains($e->getMessage(), 'operation_number_unique')) {
                        $retryCount++;
                        if ($retryCount < $maxRetries) {
                            // Wait a small random time before retrying
                            usleep(rand(100000, 500000)); // 100-500ms
                            continue;
                        } else {
                            throw new \Exception('Failed to generate unique operation number after ' . $maxRetries . ' attempts.');
                        }
                    } else {
                        // Re-throw if it's not a duplicate operation number error
                        throw $e;
                    }
                }
            }

            if (!$operation) {
                throw new \Exception('Failed to create incoming operation.');
            }

            // Handle delivery note file upload
            if ($request->hasFile('delivery_note_file')) {
                $file = $request->file('delivery_note_file');
                $filename = 'delivery_note_' . time() . '_' . $file->getClientOriginalName();
                $uploadPath = 'uploads/delivery-notes/' . $operation->id;

                // Create directory if it doesn't exist
                $fullPath = public_path($uploadPath);
                if (!file_exists($fullPath)) {
                    mkdir($fullPath, 0755, true);
                }

                // Move file to public directory
                $file->move($fullPath, $filename);
                $operation->delivery_note_path = $uploadPath . '/' . $filename;
            }

            // Handle delivery file upload based on operation type
            if ($request->hasFile('delivery_file')) {
                $file = $request->file('delivery_file');
                $filename = 'document_' . time() . '_' . $file->getClientOriginalName();

                // Use different folders based on operation type
                if ($operation->operation_type === 'site_return') {
                    $uploadPath = 'uploads/site-returns';
                } else {
                    $uploadPath = 'uploads/supplier-deliveries';
                }

                // Create directory if it doesn't exist
                $fullPath = public_path($uploadPath);
                if (!file_exists($fullPath)) {
                    mkdir($fullPath, 0755, true);
                }

                // Move file to public directory
                $file->move($fullPath, $filename);
                $operation->delivery_file = $uploadPath . '/' . $filename;
            }

            // Handle photo documentation
            $photoPaths = [];
            if ($request->hasFile('photos')) {
                $uploadPath = 'uploads/incoming-operations/' . $operation->id . '/photos';

                // Create directory if it doesn't exist
                $fullPath = public_path($uploadPath);
                if (!file_exists($fullPath)) {
                    mkdir($fullPath, 0755, true);
                }

                foreach ($request->file('photos') as $photo) {
                    $filename = 'photo_' . time() . '_' . $photo->getClientOriginalName();
                    $photo->move($fullPath, $filename);
                    $photoPaths[] = $uploadPath . '/' . $filename;
                }
                $operation->photo_documentation = $photoPaths;
            }

            // Create operation items
            $totalAmount = 0;
            $isConsolidatedMode = $request->reference_type === 'consolidated';

            foreach (($request->items ?? []) as $index => $itemData) {

                // For site returns, use quantity_returned field and set default pricing
                if ($operation->operation_type === 'site_return') {
                    // Check if this is a FOC return
                    if (isset($itemData['is_foc']) && $itemData['is_foc'] == '1') {
                        // FOC returns only have total quantity to return
                        $quantity = $itemData['total_qty_to_return'] ?? 0;
                        $damagedQty = 0; // FOC returns don't have damaged quantity
                    } else {
                        // Regular returns have returnable + damaged quantities
                        $returnableQty = floatval($itemData['returnable_qty'] ?? 0);
                        $damagedQty = floatval($itemData['damaged_qty'] ?? 0);
                        $quantity = $returnableQty + $damagedQty; // Total quantity returned
                    }
                    // Use unit_price from form data (passed from source reference)
                    $unitPrice = $itemData['unit_price'] ?? 0;
                    $unitPriceAed = $unitPrice; // Site returns are always in AED
                } else {
                    if (empty($itemData['item_id'])) {
                        continue;
                    }

                    $quantity = $itemData['quantity_delivered'] ?? 0;
                    $unitPrice = $itemData['unit_price'] ?? 0;
                    $unitPriceAed = $unitPrice;
                    $damagedQty = 0; // Initialize for non-site-return operations
                    if ($request->currency !== 'AED') {
                        $unitPriceAed = $unitPrice * $request->exchange_rate;
                    }
                }

                // Skip items with zero quantity
                if ($quantity <= 0) {
                    continue;
                }

                // Handle consolidated mode with FIFO allocations
                if ($isConsolidatedMode && isset($itemData['allocations']) && !empty($itemData['allocations'])) {
                    $allocations = json_decode($itemData['allocations'], true);

                    if (is_array($allocations) && count($allocations) > 0) {
                        // Calculate proportion of returnable vs damaged for distribution across allocations
                        $returnableRatio = $quantity > 0 ? ($returnableQty / $quantity) : 1;
                        $damagedRatio = $quantity > 0 ? ($damagedQty / $quantity) : 0;

                        // Create an IncomingOperationItem for each allocation source
                        foreach ($allocations as $allocation) {
                            $allocQty = floatval($allocation['allocated_qty'] ?? 0);
                            if ($allocQty <= 0) continue;

                            $allocUnitPrice = floatval($allocation['unit_price'] ?? 0);
                            $allocReturnableQty = $allocQty * $returnableRatio;
                            $allocDamagedQty = $allocQty * $damagedRatio;

                            // Determine quality status based on damaged quantity
                            // Valid values for incoming_operation_items: pending, passed, failed, conditional
                            $qualityStatus = $allocDamagedQty > 0 ? 'conditional' : 'passed';
                            $qualityNotes = $allocDamagedQty > 0 ? "Damaged qty: {$allocDamagedQty}" : null;

                            $itemCreateData = [
                                'incoming_operation_id' => $operation->id,
                                'item_id' => $itemData['item_id'],
                                'quantity_delivered' => $allocReturnableQty, // Only good quantity goes to inventory
                                'quantity_rejected' => $allocDamagedQty, // Damaged quantity tracked as rejected
                                'unit_price' => $allocUnitPrice,
                                'currency' => $request->currency,
                                'unit_price_aed' => $allocUnitPrice,
                                'batch_number' => $itemData['batch_number'] ?? null,
                                'production_date' => $itemData['production_date'] ?? null,
                                'expiry_date' => $itemData['expiry_date'] ?? null,
                                'quality_status' => $qualityStatus,
                                'quality_notes' => $qualityNotes,
                            ];

                            // Map source_type to the appropriate foreign key field
                            $sourceType = $allocation['source_type'] ?? '';
                            $sourceItemId = $allocation['source_item_id'] ?? null;
                            $isFocAllocation = $allocation['is_foc'] ?? false;

                            if ($sourceType === 'material_request' && $sourceItemId) {
                                $itemCreateData['outgoing_item_id'] = $sourceItemId;
                            } elseif ($sourceType === 'direct_delivery' && $sourceItemId) {
                                $itemCreateData['direct_delivery_item_id'] = $sourceItemId;
                            } elseif ($sourceType === 'supplier_delivery' && $sourceItemId) {
                                $itemCreateData['supplier_delivery_item_id'] = $sourceItemId;
                            } elseif ($sourceType === 'site_transfer' && $sourceItemId) {
                                $itemCreateData['material_transfer_item_id'] = $sourceItemId;
                                // Store the specific allocation_id for precise division/FOC tracking
                                if (isset($allocation['allocation_id']) && $allocation['allocation_id']) {
                                    $itemCreateData['site_transfer_allocation_id'] = $allocation['allocation_id'];
                                }
                            } elseif ($sourceType === 'foc_transfer' && $sourceItemId) {
                                // FOC transfers - use outgoing_fulfillment_id
                                $itemCreateData['outgoing_fulfillment_id'] = $sourceItemId;
                                $isFocAllocation = true;
                            }

                            // Store source type for later processing
                            $itemCreateData['storage_location'] = $sourceType; // Temporarily store source type here

                            $item = IncomingOperationItem::create($itemCreateData);

                            // The model automatically calculates total_price_aed in the boot method
                            $item->refresh();
                            $totalAmount += $item->total_price_aed;
                        }

                        // Continue to next item after processing allocations
                        continue;
                    }
                }

                // Non-consolidated modes: create single item entry

                $itemCreateData = [
                    'incoming_operation_id' => $operation->id,
                    'item_id' => $itemData['item_id'],
                    'quantity_delivered' => $quantity,
                    'unit_price' => $unitPrice,
                    'currency' => $request->currency,
                    'unit_price_aed' => $unitPriceAed,
                    'batch_number' => $itemData['batch_number'] ?? null,
                    'production_date' => $itemData['production_date'] ?? null,
                    'expiry_date' => $itemData['expiry_date'] ?? null,
                ];

                // For site returns, link to the original reference items
                if ($operation->operation_type === 'site_return') {
                    // Check for "all_items" mode where each item has its own reference
                    if (isset($itemData['reference_type']) && isset($itemData['reference_item_id'])) {
                        // Map reference_type to the appropriate field
                        $refType = $itemData['reference_type'];
                        $refItemId = $itemData['reference_item_id'];

                        if ($refType === 'material_request') {
                            $itemCreateData['outgoing_item_id'] = $refItemId;
                        } elseif ($refType === 'direct_delivery') {
                            $itemCreateData['direct_delivery_item_id'] = $refItemId;
                        } elseif ($refType === 'supplier_delivery') {
                            $itemCreateData['supplier_delivery_item_id'] = $refItemId;
                        } elseif ($refType === 'site_transfer') {
                            $itemCreateData['material_transfer_item_id'] = $refItemId;
                        }
                    } else {
                        // Standard single-reference mode
                        if (isset($itemData['outgoing_item_id'])) {
                            $itemCreateData['outgoing_item_id'] = $itemData['outgoing_item_id'];
                        }
                        if (isset($itemData['direct_delivery_item_id'])) {
                            $itemCreateData['direct_delivery_item_id'] = $itemData['direct_delivery_item_id'];
                        }
                        if (isset($itemData['material_transfer_item_id'])) {
                            $itemCreateData['material_transfer_item_id'] = $itemData['material_transfer_item_id'];
                        }
                        if (isset($itemData['supplier_delivery_item_id'])) {
                            $itemCreateData['supplier_delivery_item_id'] = $itemData['supplier_delivery_item_id'];
                        }
                        if (isset($itemData['outgoing_fulfillment_id'])) {
                            $itemCreateData['outgoing_fulfillment_id'] = $itemData['outgoing_fulfillment_id'];
                        }
                    }
                }

                $item = IncomingOperationItem::create($itemCreateData);

                // Handle damaged items for site returns
                if ($operation->operation_type === 'site_return' && $damagedQty > 0) {
                    $this->createDamagedItemEntry($operation, $item, $itemData, $damagedQty, $request);
                }

                // The model automatically calculates total_price_aed in the boot method
                $item->refresh(); // Reload to get calculated values
                $totalAmount += $item->total_price_aed;
            }

            // Update operation total amount
            $operation->total_amount = $totalAmount / $request->exchange_rate;
            $operation->save();

            $indirectIncoming = null;
            if ($operation->operation_type === 'supplier_delivery') {
                $indirectIncoming = $this->processIndirectSupplierDelivery($operation, $request);
            }

            // Handle site return - always auto-complete and update inventory immediately
            if ($operation->operation_type === 'site_return') {
                $returnStatus = $this->calculateSiteReturnStatus($operation, $request);
                $operation->status = 'completed'; // Always complete immediately
                $operation->approved_by = $request->received_by;
                $operation->approved_at = now();

                if ($operation->shouldForceClose()) {
                    $operation->approval_notes = 'Force closed: ' . $request->force_close_reason;
                } else {
                    $operation->approval_notes = 'Auto-completed site return (' . $returnStatus . ')';
                }

                $operation->save();

                // Process only returnable quantities to inventory for site returns
                $this->processSiteReturnToInventory($operation, $request);
            }
            // For supplier delivery, bypass approvals and go directly to inventory
            elseif ($operation->operation_type === 'supplier_delivery') {
                $operation->status = 'completed';
                $operation->approved_by = $request->received_by;
                $operation->approved_at = now();
                $operation->approval_notes = 'Auto-approved: Direct processing for supplier delivery';
                $operation->save();

                // Handle different delivery types
                if ($operation->delivery_type === 'project_site') {
                    // Direct delivery to project site - add to project inventory
                    $this->processProjectSiteDelivery($operation, $request);
                } elseif ($operation->delivery_type === 'foc') {
                    // Delivery to FOC inventory - add to FOC inventory
                    $this->processFocDelivery($operation, $request);
                } else {
                    // Standard warehouse delivery - add to main inventory
                    $operation->processToInventory();
                }
            } else {
                // For other operation types, check if force close is enabled
                if ($operation->shouldForceClose()) {
                    $operation->status = 'completed';
                    $operation->approved_by = $request->received_by;
                    $operation->approved_at = now();
                    $operation->approval_notes = 'Force closed by user request';
                    $operation->save();

                    // Update inventory immediately
                    $operation->processToInventory();
                } else {
                    // Normal workflow - pending processing
                    $operation->status = 'pending_processing';
                    $operation->save();
                }
            }

            DB::commit();

            return redirect()->route('warehouse.incoming-operations.show', $operation)
                           ->with('success', 'Incoming operation created successfully.');

        } catch (\Exception $e) {
            DB::rollback();

            return back()->withInput()
                        ->with('error', 'Failed to create incoming operation: ' . $e->getMessage());
        }
    }

    /**
     * Display the specified incoming operation
     */
    public function show(IncomingOperation $operation)
    {
        $operation->load([
            'supplier', 'project', 'project.projectDivision', 'projectDivision', 'receiver',
            'inspector', 'approver', 'items.item', 'lpo', 'materialRequest', 'materialTransfer'
        ]);

        $indirectIncomingReceipts = collect();
        if ($operation->operation_type === 'supplier_delivery') {
            $indirectIncomingReceipts = $this->matchingIndirectSupplierDeliveryReceipts($operation)
                ->with(['items.indirectGood'])
                ->get();
            $indirectSummary = $this->getIndirectSupplierDeliverySummary($operation);

            $operation->setAttribute(
                'total_amount',
                (float) ($operation->total_amount ?? 0) + $indirectSummary['total_amount']
            );
            $operation->setAttribute(
                'total_amount_aed',
                (float) ($operation->total_amount_aed ?? 0) + $indirectSummary['total_amount_aed']
            );
        }

        // For site returns, calculate total amount from source items if total_amount_aed is 0
        if ($operation->operation_type === 'site_return' && ($operation->total_amount_aed == 0 || $operation->total_amount_aed === null)) {
            $calculatedTotal = $operation->items->sum(function($item) {
                // If price is already set on the item, use it
                if ($item->total_price_aed > 0) {
                    return $item->total_price_aed;
                }

                // Otherwise, calculate from source item
                $unitPrice = 0;

                if ($item->outgoing_item_id) {
                    $outgoingItem = \App\Models\Warehouse\OutgoingItem::find($item->outgoing_item_id);
                    $unitPrice = $outgoingItem->unit_price ?? 0;
                } elseif ($item->direct_delivery_item_id) {
                    $ddItem = \App\Models\DirectDeliveryItem::find($item->direct_delivery_item_id);
                    $unitPrice = $ddItem->unit_price ?? 0;
                } elseif ($item->material_transfer_item_id) {
                    $mtItem = \App\Models\Warehouse\MaterialTransferRequestItem::find($item->material_transfer_item_id);
                    $unitPrice = $mtItem->estimated_unit_price ?? 0;
                } elseif ($item->supplier_delivery_item_id) {
                    $sdItem = IncomingOperationItem::find($item->supplier_delivery_item_id);
                    $unitPrice = $sdItem->unit_price_aed ?? 0;
                }

                return $item->quantity_delivered * $unitPrice;
            });

            // Set the calculated value on the operation object for display
            $operation->total_amount_aed = $calculatedTotal;
            $operation->total_amount = $calculatedTotal; // For site returns, both are in AED
        }

        return view('warehouse.incoming-operations.show', compact('operation', 'indirectIncomingReceipts'));
    }

    /**
     * Show the form for editing the specified incoming operation
     */
    public function edit(IncomingOperation $operation)
    {
        if ($operation->status === 'completed') {
            return back()->with('error', 'Cannot edit completed operations. Items have already been processed to inventory.');
        }

        $operation->load(['items.item']);

        $suppliers = Supplier::where('status', 'active')->get();
        $projects = Project::all();
        $divisions = Department::where('is_active', true)->get();
        $inspectors = User::whereHas('role', function ($query) {
            $query->whereIn('name', ['warehouse_manager', 'warehouse_supervisor']);
        })->get();
        $lpos = LpoManagement::where('status', 'approved')->get();

        $exchangeRates = [
            'AED' => 1.0000,
            'USD' => 3.6725,
            'EUR' => 4.0250
        ];

        return view('warehouse.incoming-operations.edit', compact(
            'operation', 'suppliers', 'projects', 'divisions',
            'inspectors', 'lpos', 'exchangeRates'
        ));
    }

    /**
     * Update the specified incoming operation
     */
    public function update(Request $request, IncomingOperation $operation)
    {
        if ($operation->status === 'completed') {
            return back()->with('error', 'Cannot update completed operations. Items have already been processed to inventory.');
        }

        $request->validate([
            'operation_date' => 'required|date',
            'division_id' => 'required|exists:project_divisions,id',
            'supplier_id' => 'nullable|exists:suppliers,id',
            'project_id' => 'nullable|exists:projects,id',
            'currency' => 'required|in:AED,USD,EUR',
            'exchange_rate' => 'required|numeric|min:0.0001',
            'items' => 'required|array|min:1',
            'delivery_note_file' => 'nullable|file|mimes:pdf,jpg,jpeg,png|max:10240'
        ]);

        DB::beginTransaction();

        try {
            // Update operation
            $operation->update($request->only([
                'operation_date', 'division_id', 'supplier_id', 'project_id',
                'project_phase', 'site_location', 'lpo_number', 'currency',
                'exchange_rate', 'delivery_note_number', 'vehicle_number',
                'driver_name', 'driver_contact', 'delivery_datetime',
                'delivery_instructions', 'inspector_id', 'notes'
            ]));

            // Handle file upload
            if ($request->hasFile('delivery_note_file')) {
                // Delete old file
                if ($operation->delivery_note_path) {
                    $oldFilePath = public_path($operation->delivery_note_path);
                    if (file_exists($oldFilePath)) {
                        unlink($oldFilePath);
                    }
                }

                $file = $request->file('delivery_note_file');
                $filename = 'delivery_note_' . time() . '_' . $file->getClientOriginalName();
                $uploadPath = 'uploads/delivery-notes/' . $operation->id;

                // Create directory if it doesn't exist
                $fullPath = public_path($uploadPath);
                if (!file_exists($fullPath)) {
                    mkdir($fullPath, 0755, true);
                }

                // Move file to public directory
                $file->move($fullPath, $filename);
                $operation->delivery_note_path = $uploadPath . '/' . $filename;
                $operation->save();
            }

            // Update items
            $operation->items()->delete(); // Remove existing items
            $totalAmount = 0;

            foreach ($request->items as $itemData) {
                $unitPriceAed = $itemData['unit_price'];
                if ($request->currency !== 'AED') {
                    $unitPriceAed = $itemData['unit_price'] * $request->exchange_rate;
                }

                $item = IncomingOperationItem::create([
                    'incoming_operation_id' => $operation->id,
                    'item_id' => $itemData['item_id'],
                    'quantity_delivered' => $itemData['quantity_delivered'],
                    'unit_price' => $itemData['unit_price'],
                    'currency' => $request->currency,
                    'unit_price_aed' => $unitPriceAed,
                    'batch_number' => $itemData['batch_number'] ?? null,
                    'expiry_date' => $itemData['expiry_date'] ?? null,
                    'allocated_project_id' => $itemData['allocated_project_id'] ?? $request->project_id
                ]);

                $totalAmount += $item->total_price_aed;
            }

            // Update total amount
            $operation->total_amount = $totalAmount / $request->exchange_rate;
            $operation->save();

            DB::commit();

            return redirect()->route('warehouse.incoming-operations.show', $operation)
                           ->with('success', 'Incoming operation updated successfully.');

        } catch (\Exception $e) {
            DB::rollback();
            return back()->withInput()
                        ->with('error', 'Failed to update incoming operation: ' . $e->getMessage());
        }
    }

    /**
     * Remove the specified incoming operation
     */
    public function destroy(IncomingOperation $operation)
    {
        if ($operation->status === 'completed') {
            return back()->with('error', 'Cannot delete completed operations. Items have already been processed to inventory.');
        }

        try {
            // Delete associated files
            if ($operation->delivery_note_path) {
                $filePath = public_path($operation->delivery_note_path);
                if (file_exists($filePath)) {
                    unlink($filePath);
                }
            }

            if ($operation->delivery_file) {
                $filePath = public_path($operation->delivery_file);
                if (file_exists($filePath)) {
                    unlink($filePath);
                }
            }

            if ($operation->photo_documentation) {
                foreach ($operation->photo_documentation as $photoPath) {
                    $filePath = public_path($photoPath);
                    if (file_exists($filePath)) {
                        unlink($filePath);
                    }
                }
            }

            $operation->delete();

            return redirect()->route('warehouse.incoming-operations.index')
                           ->with('success', 'Incoming operation deleted successfully.');

        } catch (\Exception $e) {
            return back()->with('error', 'Failed to delete incoming operation: ' . $e->getMessage());
        }
    }

    /**
     * Perform quality inspection
     */
    public function inspect(Request $request, IncomingOperation $operation)
    {
        if ($operation->status !== 'pending_inspection') {
            return back()->with('error', 'Operation is not pending inspection.');
        }

        $request->validate([
            'inspection_result' => 'required|in:passed,failed,conditional',
            'inspection_notes' => 'nullable|string',
            'items' => 'required|array',
            'items.*.id' => 'required|exists:incoming_operation_items,id',
            'items.*.quality_status' => 'required|in:passed,failed,conditional',
            'items.*.quality_notes' => 'nullable|string'
        ]);

        DB::beginTransaction();

        try {
            // Update operation inspection
            $operation->update([
                'status' => $request->inspection_result === 'passed' ? 'approved' : 'rejected',
                'inspection_datetime' => now(),
                'inspection_result' => $request->inspection_result,
                'inspection_notes' => $request->inspection_notes
            ]);

            // Update item quality status
            foreach ($request->items as $itemData) {
                $item = IncomingOperationItem::find($itemData['id']);
                $item->update([
                    'quality_status' => $itemData['quality_status'],
                    'quality_notes' => $itemData['quality_notes'] ?? null
                ]);
            }

            DB::commit();

            $message = $request->inspection_result === 'passed' ?
                      'Inspection completed. Operation approved.' :
                      'Inspection completed. Operation rejected.';

            return redirect()->route('warehouse.incoming-operations.show', $operation)
                           ->with('success', $message);

        } catch (\Exception $e) {
            DB::rollback();
            return back()->with('error', 'Failed to complete inspection: ' . $e->getMessage());
        }
    }

    /**
     * Complete the operation and update inventory
     */
    public function complete(IncomingOperation $operation)
    {
        // Check if operation is already completed (for direct processing operations)
        if ($operation->status === 'completed') {
            return back()->with('info', 'Operation is already completed.');
        }

        if ($operation->status !== 'approved') {
            return back()->with('error', 'Operation must be approved before completion.');
        }

        try {
            $operation->complete();

            return redirect()->route('warehouse.incoming-operations.show', $operation)
                           ->with('success', 'Operation completed successfully. Inventory updated.');

        } catch (\Exception $e) {
            return back()->with('error', 'Failed to complete operation: ' . $e->getMessage());
        }
    }

    /**
     * Print goods received note
     */
    public function print(IncomingOperation $operation)
    {
        $operation->load(['supplier', 'project', 'division', 'receiver', 'items.item']);

        return view('warehouse.incoming-operations.print', compact('operation'));
    }

    /**
     * Get items for AJAX requests
     */
    public function getItems(Request $request)
    {
        $search = $request->get('search');

        $items = Item::with('category')
                    ->where('status', 'active')
                    ->when($search, function ($query, $search) {
                        return $query->where(function ($searchQuery) use ($search) {
                            $searchQuery->where('item_code', 'like', "%{$search}%")
                                ->orWhere('item_description', 'like', "%{$search}%")
                                ->orWhere('notes', 'like', "%{$search}%");
                        });
                    })
                    ->limit(20)
                    ->get();

        return response()->json($items);
    }

    /**
     * Get LPO details for AJAX requests
     */
    public function getLpoDetails(Request $request)
    {
        $lpoNumber = $request->get('lpo_number');

        $lpo = LpoManagement::with(['items.item', 'supplier', 'project'])
                           ->where('lpo_number', $lpoNumber)
                           ->first();

        if (!$lpo) {
            return response()->json(['error' => 'LPO not found'], 404);
        }

        return response()->json($lpo);
    }

    /**
     * Get quality checklists for material category
     */
    public function getQualityChecklists(Request $request)
    {
        $category = $request->get('category');

        $checklists = QualityInspectionChecklist::getForCategory($category);

        return response()->json($checklists);
    }

    /**
     * Display supplier delivery operations page
     */
    public function supplierDeliveryIndex(Request $request)
    {
        $allDeliveries = collect();

        // Get standard supplier deliveries (IncomingOperations)
        $operationsQuery = IncomingOperation::with(['supplier', 'items', 'receiver', 'projectDivision'])
                                 ->where('operation_type', 'supplier_delivery')
                                 ->latest('operation_date');

        // Apply filters for operations
        if ($request->filled('status')) {
            $operationsQuery->where('status', $request->status);
        }

        if ($request->filled('supplier_id')) {
            $operationsQuery->where('supplier_id', $request->supplier_id);
        }

        if ($request->filled('division')) {
            $divisionFilter = $request->division;
            $operationsQuery->where(function ($query) use ($divisionFilter) {
                $query->where('division_id', $divisionFilter)
                    ->orWhere('division', $divisionFilter);
            });
        }

        if ($request->filled('lpo_status')) {
            $operationsQuery->where('lpo_status', $request->lpo_status);
        }

        if ($request->filled('date_from') && $request->filled('date_to')) {
            $operationsQuery->whereBetween('operation_date', [$request->date_from, $request->date_to]);
        }

        if ($request->filled('amount_min') && $request->filled('amount_max')) {
            $operationsQuery->whereBetween('total_amount_aed', [$request->amount_min, $request->amount_max]);
        }

        $standardDeliveries = $operationsQuery->get();

        // Get direct delivery transactions (IncomingTransactions)
        $transactionsQuery = \App\Models\Warehouse\IncomingTransaction::with(['supplier', 'items', 'receiver', 'project.projectDivision'])
                                 ->where('transaction_type', 'direct_delivery')
                                 ->latest('transaction_date');

        // Apply filters for transactions
        if ($request->filled('status')) {
            $transactionsQuery->where('status', $request->status);
        }

        if ($request->filled('supplier_id')) {
            $transactionsQuery->where('supplier_id', $request->supplier_id);
        }

        if ($request->filled('division')) {
            $divisionFilter = $request->division;
            $transactionsQuery->whereHas('project', function ($query) use ($divisionFilter) {
                $query->where('project_division_id', $divisionFilter);
            });
        }

        if ($request->filled('date_from') && $request->filled('date_to')) {
            $transactionsQuery->whereBetween('transaction_date', [$request->date_from, $request->date_to]);
        }

        if ($request->filled('amount_min') && $request->filled('amount_max')) {
            $transactionsQuery->whereBetween('total_amount', [$request->amount_min, $request->amount_max]);
        }

        $directDeliveries = $transactionsQuery->get();

        // Format standard deliveries
        $formattedStandardDeliveries = $standardDeliveries->map(function ($delivery) {
            $legacyDivision = $delivery->getAttributes()['division'] ?? null;
            $divisionName = $delivery->projectDivision?->division_name
                ?? $this->resolveProjectDivisionName($delivery->division_id, $legacyDivision);
            $indirectSummary = $this->getIndirectSupplierDeliverySummary($delivery);

            return (object) [
                'id' => $delivery->id,
                'type' => 'standard',
                'operation_number' => $delivery->operation_number,
                'operation_date' => $delivery->operation_date,
                'supplier' => $delivery->supplier,
                'status' => $delivery->status,
                'total_amount' => ($delivery->total_amount_aed ?? 0) + $indirectSummary['total_amount_aed'],
                'items_count' => $delivery->items->count() + $indirectSummary['items_count'],
                'receiver' => $delivery->receiver,
                'received_by_name' => $delivery->received_by_name,
                'lpo_number' => $delivery->lpo_number,
                'lpo_status' => $delivery->lpo_status,
                'delivery_note_number' => $delivery->delivery_note_number,
                'division' => $divisionName,
                'currency' => $delivery->currency,
                'created_at' => $delivery->created_at,
                'updated_at' => $delivery->updated_at
            ];
        });

        // Format direct deliveries
        $formattedDirectDeliveries = $directDeliveries->map(function ($delivery) {
            // Determine correct status for direct delivery
            $actualStatus = $delivery->status;

            // If delivery has items and received_by is set, it should be completed
            if ($delivery->items->count() > 0 && $delivery->received_by_name) {
                $actualStatus = 'completed';
            }
            // If it has items but no receiver, it's delivered but not received
            elseif ($delivery->items->count() > 0) {
                $actualStatus = 'delivered';
            }
            // If transaction_date is in the past, it should be delivered at minimum
            elseif ($delivery->transaction_date && $delivery->transaction_date < now()) {
                $actualStatus = 'delivered';
            }

            $divisionName = $delivery->project?->projectDivision?->division_name;

            return (object) [
                'id' => 'DD_' . $delivery->id, // Prefix to differentiate
                'type' => 'direct_delivery',
                'operation_number' => $delivery->delivery_note_number ?: 'DD-' . $delivery->id,
                'operation_date' => $delivery->transaction_date,
                'supplier' => $delivery->supplier,
                'status' => $actualStatus,
                'total_amount' => $delivery->total_amount ?? 0,
                'items_count' => $delivery->items->count(),
                'receiver' => $delivery->receiver,
                'received_by_name' => $delivery->received_by_name,
                'lpo_number' => $delivery->lpo_number,
                'lpo_status' => $delivery->lpo_status ?? null,
                'delivery_note_number' => $delivery->delivery_note_number,
                'division' => $divisionName ?? ('Direct to ' . ($delivery->project->project_name ?? 'Project')),
                'currency' => 'AED',
                'created_at' => $delivery->created_at,
                'updated_at' => $delivery->updated_at
            ];
        });

        // Combine and sort by operation_date
        $allDeliveries = collect($formattedStandardDeliveries)->merge(collect($formattedDirectDeliveries))
                                                   ->sortByDesc(function ($item) {
                                                       return $item->operation_date;
                                                   });

        // Paginate manually
        $perPage = 25;
        $currentPage = request()->input('page', 1);
        $paginatedItems = $allDeliveries->slice(($currentPage - 1) * $perPage, $perPage)->values();

        $deliveries = new \Illuminate\Pagination\LengthAwarePaginator(
            $paginatedItems,
            $allDeliveries->count(),
            $perPage,
            $currentPage,
            ['path' => request()->url()]
        );

        // Append query parameters to pagination links
        $deliveries->appends($request->all());

        // Get suppliers for filter
        $suppliers = Supplier::where('status', 'active')->get(['id', 'name']);

        // Calculate statistics (include both standard and direct deliveries)
        $standardDeliveryCount = IncomingOperation::where('operation_type', 'supplier_delivery')->count();
        $directDeliveryCount = \App\Models\Warehouse\IncomingTransaction::where('transaction_type', 'direct_delivery')->count();

        $standardPendingCount = IncomingOperation::where('operation_type', 'supplier_delivery')
                                                ->where('status', 'pending_delivery')->count();
        $directPendingCount = \App\Models\Warehouse\IncomingTransaction::where('transaction_type', 'direct_delivery')
                                                   ->where('status', 'pending')->count();

        $standardCompletedToday = IncomingOperation::where('operation_type', 'supplier_delivery')
                                                 ->where('status', 'completed')
                                                 ->whereDate('operation_date', today())->count();
        $directCompletedToday = \App\Models\Warehouse\IncomingTransaction::where('transaction_type', 'direct_delivery')
                                                     ->where('status', 'completed')
                                                     ->whereDate('transaction_date', today())->count();

        $standardTotalValue = IncomingOperation::where('operation_type', 'supplier_delivery')
                                             ->where('status', 'completed')
                                             ->sum('total_amount_aed') ?? 0;
        $directTotalValue = \App\Models\Warehouse\IncomingTransaction::where('transaction_type', 'direct_delivery')
                                                   ->where('status', 'completed')
                                                   ->sum('total_amount') ?? 0;

        $statistics = [
            'total_deliveries' => $standardDeliveryCount + $directDeliveryCount,
            'pending_delivery' => $standardPendingCount + $directPendingCount,
            'completed_today' => $standardCompletedToday + $directCompletedToday,
            'total_value_aed' => $standardTotalValue + $directTotalValue,
        ];

        // Get top suppliers (include both standard and direct deliveries)
        $topSuppliers = Supplier::withCount([
            'incomingOperations as standard_deliveries_count' => function ($query) {
                $query->where('operation_type', 'supplier_delivery');
            }
        ])->get()->map(function ($supplier) {
            // Count direct deliveries for this supplier
            $directDeliveryCount = \App\Models\Warehouse\IncomingTransaction::where('supplier_id', $supplier->id)
                                                         ->where('transaction_type', 'direct_delivery')
                                                         ->count();

            $supplier->deliveries_count = $supplier->standard_deliveries_count + $directDeliveryCount;
            return $supplier;
        })->sortByDesc(function ($supplier) {
            return $supplier->deliveries_count;
        })->take(5);

        // Get divisions for filter
        $divisions = ProjectDivision::where('status', 'active')
            ->orderBy('division_name')
            ->get(['id', 'division_name', 'division_code']);

        return view('warehouse.supplier-delivery.index', compact(
            'deliveries',
            'suppliers',
            'divisions',
            'statistics',
            'topSuppliers'
        ));
    }

    /**
     * Show a narrow edit form for supplier delivery header details.
     */
    public function supplierDeliveryEdit(IncomingOperation $operation)
    {
        if ($operation->operation_type !== 'supplier_delivery') {
            abort(404);
        }

        $operation->load(['supplier', 'projectDivision', 'receiver', 'items.item']);

        return view('warehouse.supplier-delivery.edit', compact('operation'));
    }

    /**
     * Update supplier delivery header details without touching stock items.
     */
    public function supplierDeliveryUpdate(Request $request, IncomingOperation $operation)
    {
        if ($operation->operation_type !== 'supplier_delivery') {
            abort(404);
        }

        $validated = $request->validate([
            'lpo_number' => 'nullable|string|max:255',
            'lpo_status' => ['nullable', Rule::in(['pending', 'approved', 'partially_delivered', 'completed', 'cancelled'])],
            'delivery_note_number' => 'nullable|string|max:255',
            'notes' => 'nullable|string|max:5000',
        ]);

        $lpoNumber = trim((string) ($validated['lpo_number'] ?? ''));
        $lpoStatus = $validated['lpo_status'] ?? null;

        if ($lpoNumber === '') {
            $lpoNumber = null;
            $lpoStatus = $lpoStatus ?: 'pending';
        } elseif (!$lpoStatus || $lpoStatus === 'pending') {
            $lpoStatus = 'approved';
        }

        $operation->update([
            'lpo_number' => $lpoNumber,
            'lpo_status' => $lpoStatus,
            'delivery_note_number' => $validated['delivery_note_number'] ?? null,
            'notes' => $validated['notes'] ?? null,
        ]);

        return redirect()
            ->route('warehouse.supplier-delivery.index')
            ->with('success', 'Supplier delivery updated successfully.');
    }

    /**
     * Show the form for creating a new supplier delivery
     */
    public function supplierDeliveryCreate()
    {
        // Get suppliers for dropdown
        $suppliers = Supplier::where('status', 'active')
                            ->orderBy('name')
                            ->get(['id', 'name', 'supplier_code']);

        // Get users for received by dropdown
        $users = User::orderBy('name')
                    ->get(['id', 'name']);

        // Get projects for project site delivery dropdown
        $projects = Project::orderBy('project_name')
                          ->get(['id', 'project_name', 'project_number']);

        // Get divisions for dropdown
        $divisions = ProjectDivision::where('status', 'active')
                                   ->orderBy('division_name')
                                   ->get(['id', 'division_name', 'division_code']);

        // Get items for dropdown
        $items = \App\Models\Warehouse\Item::where('status', 'active')
                            ->orderBy('item_description')
                            ->get(['id', 'item_code', 'item_description', 'unit_of_measure', 'notes']);

        $indirectGoods = IndirectGood::where('status', 'active')
                            ->orderBy('ig_description')
                            ->get(['id', 'ig_code', 'ig_description', 'unit_of_measure', 'notes']);

        return view('warehouse.supplier-delivery.create', compact(
            'suppliers',
            'users',
            'projects',
            'divisions',
            'items',
            'indirectGoods'
        ));
    }

    /**
     * Resolve the user-facing project division name for supplier delivery lists.
     */
    private function resolveProjectDivisionName($divisionId = null, $legacyDivision = null): ?string
    {
        static $divisionNamesById = null;

        if ($divisionNamesById === null) {
            $divisionNamesById = ProjectDivision::query()
                ->get(['id', 'division_name', 'division_code'])
                ->flatMap(function ($division) {
                    $keys = [
                        (string) $division->id => $division->division_name,
                        (string) $division->division_name => $division->division_name,
                    ];

                    if ($division->division_code) {
                        $keys[(string) $division->division_code] = $division->division_name;
                    }

                    return $keys;
                })
                ->filter()
                ->all();
        }

        if ($divisionId && isset($divisionNamesById[(string) $divisionId])) {
            return $divisionNamesById[(string) $divisionId];
        }

        if ($legacyDivision && isset($divisionNamesById[(string) $legacyDivision])) {
            return $divisionNamesById[(string) $legacyDivision];
        }

        return $legacyDivision && !is_numeric($legacyDivision) ? $legacyDivision : null;
    }

    /**
     * Find indirect goods receipts created from a supplier delivery operation.
     */
    private function matchingIndirectSupplierDeliveryReceipts(IncomingOperation $operation)
    {
        return IndirectGoodsIncoming::query()
            ->where('supplier_id', $operation->supplier_id)
            ->whereDate('receipt_date', $operation->operation_date)
            ->where(function ($query) use ($operation) {
                if ($operation->delivery_note_number) {
                    $query->where('delivery_note_number', $operation->delivery_note_number)
                        ->orWhere('invoice_number', $operation->delivery_note_number);
                }

                $query->orWhere('notes', 'LIKE', '%' . $operation->operation_number . '%');
            });
    }

    /**
     * Summarize indirect goods item lines and AED value for a supplier delivery operation.
     */
    private function getIndirectSupplierDeliverySummary(IncomingOperation $operation): array
    {
        $receipts = $this->matchingIndirectSupplierDeliveryReceipts($operation)
            ->withCount('items')
            ->get();

        $totalAmountAed = $receipts->sum(function ($receipt) use ($operation) {
            $total = (float) $receipt->total_amount;

            if (($receipt->currency ?? 'AED') !== 'AED') {
                return $total * (float) ($operation->exchange_rate ?: 1);
            }

            return $total;
        });

        return [
            'items_count' => (int) $receipts->sum('items_count'),
            'total_amount' => (float) $receipts->sum('total_amount'),
            'total_amount_aed' => $totalAmountAed,
        ];
    }

    /**
     * Get supplier delivery data for AJAX
     */
    public function supplierDeliveryData(Request $request)
    {
        $query = IncomingOperation::with(['supplier', 'items', 'receiver', 'projectDivision'])
                                 ->where('operation_type', 'supplier_delivery');

        // Apply filters if provided
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('division')) {
            $divisionFilter = $request->division;
            $query->where(function ($query) use ($divisionFilter) {
                $query->where('division_id', $divisionFilter)
                    ->orWhere('division', $divisionFilter);
            });
        }

        $deliveries = $query->latest('operation_date')->get()->map(function ($delivery) {
            $legacyDivision = $delivery->getAttributes()['division'] ?? null;
            $divisionName = $delivery->projectDivision?->division_name
                ?? $this->resolveProjectDivisionName($delivery->division_id, $legacyDivision);
            $indirectSummary = $this->getIndirectSupplierDeliverySummary($delivery);
            $delivery->setAttribute('division', $divisionName);
            $delivery->setAttribute('division_name', $divisionName);
            $delivery->setAttribute(
                'items_count',
                $delivery->items->count() + $indirectSummary['items_count']
            );
            $delivery->setAttribute(
                'total_amount_aed',
                ($delivery->total_amount_aed ?? 0) + $indirectSummary['total_amount_aed']
            );
            $delivery->setAttribute(
                'total_amount',
                ($delivery->total_amount ?? 0) + $indirectSummary['total_amount']
            );

            return $delivery;
        });

        return response()->json([
            'data' => $deliveries,
            'recordsTotal' => $deliveries->count(),
            'recordsFiltered' => $deliveries->count()
        ]);
    }

    /**
     * Export supplier deliveries
     */
    public function exportSupplierDeliveries(Request $request)
    {
        // This is a placeholder for export functionality
        // You would implement actual export logic here (Excel, CSV, etc.)

        return response()->json([
            'success' => false,
            'message' => 'Export functionality will be implemented soon'
        ]);
    }

    /**
     * Display site return operations page
     */
    public function siteReturnIndex(Request $request)
    {
        // Get projects that have site returns with aggregated data
        $projectReturns = IncomingOperation::with(['project', 'project.projectDivision'])
            ->leftJoin('incoming_operation_items', 'incoming_operations.id', '=', 'incoming_operation_items.incoming_operation_id')
            ->leftJoin('outgoing_items', 'incoming_operation_items.outgoing_item_id', '=', 'outgoing_items.id')
            ->leftJoin('direct_delivery_items', 'incoming_operation_items.direct_delivery_item_id', '=', 'direct_delivery_items.id')
            ->where('incoming_operations.operation_type', 'site_return')
            ->select(
                \DB::raw('COALESCE(incoming_operations.project_id, 0) as project_id'),
                \DB::raw('COUNT(DISTINCT incoming_operations.id) as total_returns'),
                \DB::raw('COUNT(DISTINCT incoming_operations.material_request_id) as material_requests_count'),
                \DB::raw('MAX(incoming_operations.operation_date) as latest_return_date'),
                \DB::raw('COALESCE(SUM(incoming_operation_items.quantity_delivered), 0) as total_items_returned'),
                \DB::raw('SUM(
                    incoming_operation_items.quantity_delivered * 
                    COALESCE(
                        outgoing_items.unit_price,
                        direct_delivery_items.unit_price,
                        incoming_operation_items.unit_price,
                        0
                    )
                ) as total_value_returned')
            )
            ->groupBy(\DB::raw('COALESCE(incoming_operations.project_id, 0)'))
            ->having('total_returns', '>', 0);

        // Apply project filter if provided
        if ($request->filled('project_id')) {
            $projectReturns->where('incoming_operations.project_id', $request->project_id);
        }

        // Apply division filter if provided
        if ($request->filled('division')) {
            $projectReturns->whereHas('project.projectDivision', function($q) use ($request) {
                $q->where('division_name', $request->division);
            });
        }

        // Apply search filter if provided (search by project name or number)
        if ($request->filled('search')) {
            $search = $request->search;
            $projectReturns->whereHas('project', function($q) use ($search) {
                $q->where('project_name', 'like', "%{$search}%")
                  ->orWhere('project_number', 'like', "%{$search}%");
            });
        }

        $projectReturns = $projectReturns->get();

        // Load project details and add status information
        $projects = $projectReturns->map(function ($returnData) {
            if ($returnData->project_id == 0) {
                // Handle unassigned returns (no project)
                $statuses = IncomingOperation::whereNull('project_id')
                    ->where('operation_type', 'site_return')
                    ->select('status', \DB::raw('COUNT(*) as count'))
                    ->groupBy('status')
                    ->pluck('count', 'status')
                    ->toArray();

                return (object) [
                    'project_id' => 0,
                    'project_name' => 'Unassigned Returns',
                    'project_number' => 'N/A',
                    'project_division' => 'N/A',
                    'total_returns' => $returnData->total_returns,
                    'material_requests_count' => $returnData->material_requests_count,
                    'latest_return_date' => $returnData->latest_return_date,
                    'total_items_returned' => $returnData->total_items_returned,
                    'total_value_returned' => $returnData->total_value_returned,
                    'status_breakdown' => $statuses,
                    'primary_status' => $this->getPrimaryProjectStatus($statuses),
                ];
            } else {
                $project = \App\Models\Warehouse\Project::with(['projectDivision'])->find($returnData->project_id);

                if ($project) {
                    // Get status breakdown for this project
                    $statuses = IncomingOperation::where('project_id', $returnData->project_id)
                        ->where('operation_type', 'site_return')
                        ->select('status', \DB::raw('COUNT(*) as count'))
                        ->groupBy('status')
                        ->pluck('count', 'status')
                        ->toArray();

                    return (object) [
                        'project_id' => $project->id,
                        'project_name' => $project->project_name,
                        'project_number' => $project->project_number,
                        'project_division' => $project->projectDivision ? $project->projectDivision->division_name : 'N/A',
                        'total_returns' => $returnData->total_returns,
                        'material_requests_count' => $returnData->material_requests_count,
                        'latest_return_date' => $returnData->latest_return_date,
                        'total_items_returned' => $returnData->total_items_returned,
                        'total_value_returned' => $returnData->total_value_returned,
                        'status_breakdown' => $statuses,
                        'primary_status' => $this->getPrimaryProjectStatus($statuses),
                    ];
                }
            }
            return null;
        })->filter();

        // Apply sorting based on request
        $sort = $request->get('sort', 'latest_return');
        switch ($sort) {
            case 'project_name':
                $projects = $projects->sortBy('project_name');
                break;
            case 'most_returns':
                $projects = $projects->sortByDesc('total_returns');
                break;
            case 'total_items':
                $projects = $projects->sortByDesc('total_items_returned');
                break;
            case 'latest_return':
            default:
                $projects = $projects->sortByDesc('latest_return_date');
                break;
        }

        // Get all projects for filter dropdown
        $allProjects = \App\Models\Warehouse\Project::orderBy('project_name')->get();

        // Calculate statistics
        $statistics = [
            'total_projects_with_returns' => $projects->count(),
            'total_returns' => $projects->sum('total_returns'),
            'total_material_requests' => $projects->sum('material_requests_count'),
            'total_items_returned' => $projects->sum('total_items_returned'),
        ];

        return view('warehouse.site-return.index', compact('projects', 'allProjects', 'statistics'));
    }

    /**
     * Get primary status for a project based on status breakdown
     */
    private function getPrimaryProjectStatus($statuses)
    {
        if (empty($statuses)) {
            return 'no_returns';
        }

        // Priority order for statuses
        $statusPriority = [
            'pending_processing' => 4,
            'processing' => 3,
            'processed' => 2,
            'completed' => 1,
            'rejected' => 5
        ];

        $primaryStatus = 'unknown';
        $highestPriority = 999;

        foreach ($statuses as $status => $count) {
            $priority = $statusPriority[$status] ?? 6;
            if ($priority < $highestPriority) {
                $highestPriority = $priority;
                $primaryStatus = $status;
            }
        }

        return $primaryStatus;
    }

    /**
     * Show detailed material returns for a specific project
     */
    public function showProjectReturns($projectId)
    {
        // Get the project
        $project = \App\Models\Warehouse\Project::with(['projectDivision'])
            ->findOrFail($projectId);

        // Get all site returns for this project grouped by material request
        $query = IncomingOperation::with(['items.item', 'receiver', 'materialRequest', 'materialRequest.requester'])
            ->where('operation_type', 'site_return')
            ->where('project_id', $projectId);

        $allReturns = $query->orderBy('operation_date', 'desc')->get();

        // Group by material request ID
        $groupedReturns = $allReturns->groupBy('material_request_id');

        // Get consolidated return data
        $returns = collect();
        foreach ($groupedReturns as $mrId => $mrReturns) {
            $consolidatedReturn = $mrReturns->first();
            $consolidatedReturn->return_count = $mrReturns->count();
            $consolidatedReturn->all_returns = $mrReturns;
            $consolidatedReturn->total_returned_items = $mrReturns->sum(function($return) {
                return $return->items->sum('quantity_delivered');
            });
            $consolidatedReturn->latest_return_date = $mrReturns->max('operation_date');
            // Calculate total amount from items' total_price_aed
            // If total_price_aed is 0, calculate from source item prices
            $consolidatedReturn->total_amount = $mrReturns->sum(function($return) {
                return $return->items->sum(function($item) {
                    // If price is already set, use it
                    if ($item->total_price_aed > 0) {
                        return $item->total_price_aed;
                    }

                    // Otherwise, calculate from source item
                    $unitPrice = 0;

                    if ($item->outgoing_item_id) {
                        $outgoingItem = \App\Models\Warehouse\OutgoingItem::find($item->outgoing_item_id);
                        $unitPrice = $outgoingItem->unit_price ?? 0;
                    } elseif ($item->direct_delivery_item_id) {
                        $ddItem = \App\Models\DirectDeliveryItem::find($item->direct_delivery_item_id);
                        $unitPrice = $ddItem->unit_price ?? 0;
                    } elseif ($item->material_transfer_item_id) {
                        $mtItem = \App\Models\Warehouse\MaterialTransferRequestItem::find($item->material_transfer_item_id);
                        $unitPrice = $mtItem->estimated_unit_price ?? 0;
                    } elseif ($item->supplier_delivery_item_id) {
                        $sdItem = \App\Models\Warehouse\IncomingOperationItem::find($item->supplier_delivery_item_id);
                        $unitPrice = $sdItem->unit_price_aed ?? 0;
                    }

                    return $item->quantity_delivered * $unitPrice;
                });
            });

            // Calculate consolidated status
            if ($consolidatedReturn->material_request_id) {
                $materialRequest = OutgoingTransaction::with('items')->find($consolidatedReturn->material_request_id);
                $consolidatedReturn->consolidated_status = $this->calculateMRReturnStatus($materialRequest, $mrReturns);
            } else {
                $consolidatedReturn->consolidated_status = $this->calculateConsolidatedStatus($mrReturns);
            }

            $returns->push($consolidatedReturn);
        }

        // Project statistics
        // Calculate total value by summing items' total_price_aed directly
        // If total_price_aed is 0, calculate from source item prices
        $totalValueReturned = $allReturns->sum(function($return) {
            return $return->items->sum(function($item) {
                // If price is already set, use it
                if ($item->total_price_aed > 0) {
                    return $item->total_price_aed;
                }

                // Otherwise, calculate from source item
                $unitPrice = 0;

                if ($item->outgoing_item_id) {
                    $outgoingItem = \App\Models\Warehouse\OutgoingItem::find($item->outgoing_item_id);
                    $unitPrice = $outgoingItem->unit_price ?? 0;
                } elseif ($item->direct_delivery_item_id) {
                    $ddItem = \App\Models\DirectDeliveryItem::find($item->direct_delivery_item_id);
                    $unitPrice = $ddItem->unit_price ?? 0;
                } elseif ($item->material_transfer_item_id) {
                    $mtItem = \App\Models\Warehouse\MaterialTransferRequestItem::find($item->material_transfer_item_id);
                    $unitPrice = $mtItem->estimated_unit_price ?? 0;
                } elseif ($item->supplier_delivery_item_id) {
                    $sdItem = \App\Models\Warehouse\IncomingOperationItem::find($item->supplier_delivery_item_id);
                    $unitPrice = $sdItem->unit_price_aed ?? 0;
                }

                return $item->quantity_delivered * $unitPrice;
            });
        });

        $statistics = [
            'total_returns' => $allReturns->count(),
            'total_material_requests' => $groupedReturns->count(),
            'total_items_returned' => $allReturns->sum(function($return) {
                return $return->items->sum('quantity_delivered');
            }),
            'total_value_returned' => $totalValueReturned,
            'latest_return_date' => $allReturns->max('operation_date'),
        ];

        return view('warehouse.site-return.project-details', compact('project', 'returns', 'statistics'));
    }

    /**
     * Calculate consolidated status for multiple returns against same material request
     */
    private function calculateConsolidatedStatus($returns)
    {
        $statuses = $returns->pluck('status')->unique();

        if ($statuses->contains('pending_processing')) {
            return 'pending_processing';
        } elseif ($statuses->count() === 1) {
            return $statuses->first();
        } else {
            return 'mixed'; // Multiple different statuses
        }
    }


    /**
     * Show all returns for a specific Material Request
     */
    public function showMaterialRequestReturns($materialRequestId)
    {
        // Get the material request (outgoing transaction)
        $materialRequest = OutgoingTransaction::with(['project', 'division', 'requester', 'items'])
                                             ->findOrFail($materialRequestId);

        // Get all site returns for this material request
        $returns = IncomingOperation::with(['items.item', 'receiver', 'materialRequest'])
                                  ->where('operation_type', 'site_return')
                                  ->where('material_request_id', $materialRequestId)
                                  ->orderBy('operation_date', 'desc')
                                  ->get();

        // Calculate summary statistics
        $totalIssued = $materialRequest->items->sum('quantity_released');
        $totalReturned = $returns->sum(function($return) {
            return $return->items->sum('quantity_delivered');
        });

        $summary = [
            'total_returns' => $returns->count(),
            'total_items_returned' => $totalReturned,
            'pending_quantity' => $totalIssued - $totalReturned,
            'latest_return_date' => $returns->first()?->operation_date,
            'completion_status' => $this->calculateMRReturnStatus($materialRequest, $returns)
        ];

        return view('warehouse.site-return.mr-returns', compact(
            'materialRequest', 'returns', 'summary'
        ));
    }

    /**
     * Calculate return completion status for Material Request
     */
    private function calculateMRReturnStatus($materialRequest, $returns)
    {
        // Handle case where material request doesn't exist
        if (!$materialRequest) {
            return 'unknown';
        }

        $totalIssued = $materialRequest->items->sum('quantity_released');
        $totalReturned = $returns->sum(function($return) {
            return $return->items->sum('quantity_delivered');
        });

        if ($totalReturned == 0) {
            return 'not_returned';
        } elseif ($totalReturned < $totalIssued) {
            return 'partially_returned';
        } elseif ($totalReturned >= $totalIssued) {
            return 'fully_returned';
        }

        return 'unknown';
    }

    /**
     * Show the form for creating a new site return
     */
    public function siteReturnCreate()
    {
        // Get projects for dropdown
        $projects = Project::orderBy('project_name')
                          ->get(['id', 'project_name', 'project_number']);

        // Get users for received by dropdown
        $users = User::orderBy('name')
                    ->get(['id', 'name']);

        // Get items for dropdown
        $items = Item::where('status', 'active')
                    ->orderBy('item_description')
                    ->get(['id', 'item_code', 'item_description', 'unit_of_measure']);

        // Don't load all material requests by default - will be loaded via AJAX based on project selection
        $materialRequests = collect(); // Empty collection

        // Don't load all direct deliveries by default - will be loaded via AJAX based on project selection
        $directDeliveries = collect(); // Empty collection

        return view('warehouse.site-return.create', compact(
            'projects',
            'users',
            'items',
            'materialRequests',
            'directDeliveries'
        ));
    }

    /**
     * Get material request details for site return
     */
    public function getMaterialRequest($id)
    {
        \Log::info('getMaterialRequest called with ID: ' . $id);

        try {
            $materialRequest = OutgoingTransaction::with([
                'project',
                'requester',
                'division',
                'items.item',
                'items.division'
            ])->findOrFail($id);

            \Log::info('Material request found:', ['id' => $materialRequest->id, 'status' => $materialRequest->status]);

            // Only allow successful material requests to be returned
            if ($materialRequest->status !== 'successful') {
                return response()->json([
                    'success' => false,
                    'message' => 'Only successfully dispatched material requests can be returned.'
                ]);
            }

            // Get division name - first try transaction level, then from items
            $divisionName = null;
            if ($materialRequest->division) {
                $divisionName = $materialRequest->division->division_name;
            } else {
                // Get unique division names from items
                $itemDivisions = $materialRequest->items
                    ->filter(fn($item) => $item->division)
                    ->map(fn($item) => $item->division->division_name)
                    ->unique()
                    ->values();

                if ($itemDivisions->count() === 1) {
                    $divisionName = $itemDivisions->first();
                } elseif ($itemDivisions->count() > 1) {
                    $divisionName = $itemDivisions->implode(', ');
                }
            }

            $data = [
                'id' => $materialRequest->id,
                'reference_number' => $materialRequest->material_request_number ?: 'OUT-' . str_pad($materialRequest->id, 6, '0', STR_PAD_LEFT),
                'transaction_date' => $materialRequest->transaction_date->format('M d, Y'),
                'project_id' => $materialRequest->project_id,
                'project_name' => $materialRequest->project ? $materialRequest->project->project_name : 'Unknown Project',
                'division_name' => $divisionName,
                'requested_by' => $materialRequest->requester ? $materialRequest->requester->name : 'Unknown User',
                'status' => ucfirst($materialRequest->status),
                'items' => $materialRequest->items->map(function($item) {
                    // Calculate total previously returned quantity for this outgoing item (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($query) {
                        $query->where('operation_type', 'site_return')
                              ->where('status', 'completed');
                    })
                    ->where('outgoing_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate quantity transferred to other projects via Material Transfers
                    $transferredToOtherProjects = \App\Models\Warehouse\MaterialTransferRequestItem::whereHas('transferRequest', function($query) {
                        $query->whereIn('status', ['approved', 'partially_approved']);
                    })
                    ->where('outgoing_item_id', $item->id)
                    ->sum('quantity_approved');

                    $quantityReleased = $item->quantity_released ?? 0;
                    $remainingReturnable = max(0, $quantityReleased - $previouslyReturned - $transferredToOtherProjects);


                    return [
                        'id' => $item->id,
                        'item_id' => $item->item_id,
                        'item_code' => $item->item ? $item->item->item_code : 'N/A',
                        'item_description' => $item->item ? $item->item->item_description : 'Unknown Item',
                        'unit' => $item->item ? $item->item->unit_of_measure : 'N/A',
                        'division_name' => $item->division ? $item->division->division_name : 'No Division',
                        'quantity_requested' => $item->quantity_requested,
                        'quantity_released' => $quantityReleased,
                        'previously_returned' => $previouslyReturned,
                        'transferred_to_projects' => $transferredToOtherProjects,
                        'remaining_returnable' => $remainingReturnable,
                    ];
                })
            ];

            \Log::info('Returning successful response', $data);

            return response()->json([
                'success' => true,
                'data' => $data
            ]);

        } catch (\Exception $e) {
            \Log::error('Error in getMaterialRequest', [
                'id' => $id,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Material request not found or error loading details.'
            ]);
        }
    }

    /**
     * Get direct delivery details for site return
     */
    public function getDirectDelivery($id)
    {
        \Log::info('getDirectDelivery called with ID: ' . $id);

        try {
            $directDelivery = \App\Models\DirectDelivery::with([
                'project',
                'supplier',
                'division',
                'items.item'
            ])->findOrFail($id);

            \Log::info('Direct delivery found:', ['id' => $directDelivery->id, 'status' => $directDelivery->status]);

            // Allow pending, completed, and delivered direct deliveries to be returned
            if (!in_array($directDelivery->status, ['delivered', 'completed', 'pending'])) {
                return response()->json([
                    'success' => false,
                    'message' => 'This direct delivery status does not allow returns.'
                ]);
            }

            // Check if it can be returned
            if (!$directDelivery->can_be_returned) {
                return response()->json([
                    'success' => false,
                    'message' => 'This direct delivery is not eligible for returns.'
                ]);
            }

            $data = [
                'id' => $directDelivery->id,
                'reference_number' => $directDelivery->delivery_number,
                'delivery_date' => $directDelivery->delivery_date->format('M d, Y'),
                'project_id' => $directDelivery->project_id,
                'project_name' => $directDelivery->project ? $directDelivery->project->project_name : 'Unknown Project',
                'supplier_name' => $directDelivery->supplier ? $directDelivery->supplier->name : 'No Supplier',
                'division_name' => $directDelivery->division ? $directDelivery->division->division_name : 'No Division',
                'status' => ucfirst(str_replace('_', ' ', $directDelivery->status)),
                'items' => $directDelivery->items->map(function($item) use ($directDelivery) {
                    // Calculate total previously returned quantity for this direct delivery item (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($query) {
                        $query->where('operation_type', 'site_return')
                              ->where('status', 'completed');
                    })
                    ->where('direct_delivery_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate quantity transferred to other projects via Material Transfers
                    $transferredToOtherProjects = \App\Models\Warehouse\MaterialTransferRequestItem::whereHas('transferRequest', function($query) {
                        $query->whereIn('status', ['approved', 'partially_approved']);
                    })
                    ->where('direct_delivery_item_id', $item->id)
                    ->sum('quantity_approved');

                    // Use delivered_quantity if available, otherwise use ordered_quantity for pending deliveries
                    $deliveredQuantity = $item->delivered_quantity ?? 0;
                    $baseQuantity = ($deliveredQuantity > 0) ? $deliveredQuantity : $item->ordered_quantity;
                    $remainingReturnable = max(0, $baseQuantity - $previouslyReturned - $transferredToOtherProjects);

                    return [
                        'id' => $item->id,
                        'item_id' => $item->item_id,
                        'item_code' => $item->item ? $item->item->item_code : 'N/A',
                        'item_description' => $item->item ? $item->item->item_description : 'Unknown Item',
                        'unit' => $item->unit_of_measure ?: ($item->item ? $item->item->unit_of_measure : 'N/A'),
                        'division_name' => $directDelivery->division ? $directDelivery->division->division_name : 'No Division',
                        'quantity_ordered' => $item->ordered_quantity,
                        'quantity_delivered' => $baseQuantity,
                        'previously_returned' => $previouslyReturned,
                        'transferred_to_projects' => $transferredToOtherProjects,
                        'remaining_returnable' => $remainingReturnable,
                        'batch_number' => $item->batch_number,
                        'unit_price' => $item->unit_price,
                    ];
                })
            ];

            \Log::info('Returning successful response', $data);

            return response()->json([
                'success' => true,
                'data' => $data
            ]);

        } catch (\Exception $e) {
            \Log::error('Error in getDirectDelivery', [
                'id' => $id,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Direct delivery not found or error loading details.'
            ]);
        }
    }

    /**
     * Get material requests by project for site return
     */
    public function getMaterialRequestsByProject($projectId)
    {
        try {
            $materialRequests = OutgoingTransaction::with(['project', 'requester'])
                ->where('project_id', $projectId)
                ->where('status', 'successful')
                ->orderBy('created_at', 'desc')
                ->get()
                ->map(function($request) {
                    return [
                        'id' => $request->id,
                        'text' => ($request->material_request_number ?: 'OUT-' . str_pad($request->id, 6, '0', STR_PAD_LEFT)) .
                               ' - ' . ($request->requester->name ?? 'Unknown') .
                               ' (' . $request->transaction_date->format('M d, Y') . ')',
                        'material_request_number' => $request->material_request_number ?: 'OUT-' . str_pad($request->id, 6, '0', STR_PAD_LEFT),
                        'requester_name' => $request->requester->name ?? 'Unknown',
                        'transaction_date' => $request->transaction_date->format('M d, Y')
                    ];
                });

            return response()->json([
                'success' => true,
                'data' => $materialRequests
            ]);

        } catch (\Exception $e) {
            \Log::error('Error in getMaterialRequestsByProject', [
                'project_id' => $projectId,
                'error' => $e->getMessage()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Error loading material requests for this project.'
            ]);
        }
    }

    /**
     * Get direct deliveries by project for site return dropdown
     */
    public function getDirectDeliveriesByProject($projectId)
    {
        try {
            $directDeliveries = \App\Models\DirectDelivery::with(['project', 'supplier'])
                ->where('project_id', $projectId)
                ->where('status', 'delivered')
                ->where('can_be_returned', true)
                ->orderBy('delivery_date', 'desc')
                ->get()
                ->map(function($delivery) {
                    return [
                        'id' => $delivery->id,
                        'text' => $delivery->delivery_number .
                               ' - ' . $delivery->project->project_name .
                               ($delivery->supplier ? ' (' . $delivery->supplier->name . ')' : '') .
                               ' - ' . $delivery->delivery_date->format('M d, Y'),
                        'delivery_number' => $delivery->delivery_number,
                        'supplier_name' => $delivery->supplier->name ?? 'No Supplier',
                        'delivery_date' => $delivery->delivery_date->format('M d, Y')
                    ];
                });

            return response()->json([
                'success' => true,
                'data' => $directDeliveries
            ]);

        } catch (\Exception $e) {
            \Log::error('Error in getDirectDeliveriesByProject', [
                'project_id' => $projectId,
                'error' => $e->getMessage()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Error loading direct deliveries for this project.'
            ]);
        }
    }

    /**
     * Get combined references (Material Requests + Direct Deliveries) by project for site return
     * Sorted by date in descending order (newest first)
     */
    public function getCombinedReferencesByProject($projectId)
    {
        try {
            \Log::info('getCombinedReferencesByProject called', ['project_id' => $projectId]);

            $combinedReferences = [];

            // Get Material Requests (excluding those with closed returns)
            $materialRequests = OutgoingTransaction::with(['project', 'requester'])
                ->where('project_id', $projectId)
                ->where('status', 'successful')
                ->get(); // Simplified - removed complex whereDoesntHave for now

            \Log::info('Found material requests', ['count' => $materialRequests->count()]);

            $materialRequests = $materialRequests->map(function($request) {
                    // Use the same logic as in other methods to get the reference number
                    $materialRequestNumber = $request->material_request_number ?: 'OUT-' . str_pad($request->id, 6, '0', STR_PAD_LEFT);

                    return [
                        'id' => 'MR_' . $request->id,
                        'reference_id' => $request->id,
                        'reference_type' => 'material_request',
                        'display_text' => 'MR: ' . $materialRequestNumber . ' (' . $request->transaction_date->format('M d, Y') . ')',
                        'sort_date' => $request->transaction_date,
                        'details' => [
                            'number' => $materialRequestNumber,
                            'date' => $request->transaction_date->format('M d, Y'),
                            'requester' => $request->requester ? $request->requester->name : 'Unknown',
                            'project' => $request->project ? $request->project->project_name : 'Unknown'
                        ]
                    ];
                });

            // Get Direct Deliveries (excluding those with closed returns)
            $directDeliveries = \App\Models\DirectDelivery::with(['project', 'supplier'])
                ->where('project_id', $projectId)
                ->whereIn('status', ['delivered', 'completed', 'pending'])
                ->get(); // Simplified - removed complex conditions for now

            \Log::info('Found direct deliveries', ['count' => $directDeliveries->count()]);

            $directDeliveries = $directDeliveries->map(function($delivery) {
                    return [
                        'id' => 'DD_' . $delivery->id,
                        'reference_id' => $delivery->id,
                        'reference_type' => 'direct_delivery',
                        'display_text' => 'DD: ' . $delivery->delivery_number . ' (' . $delivery->delivery_date->format('M d, Y') . ')',
                        'sort_date' => $delivery->delivery_date,
                        'details' => [
                            'number' => $delivery->delivery_number,
                            'date' => $delivery->delivery_date->format('M d, Y'),
                            'supplier' => $delivery->supplier ? $delivery->supplier->name : 'No Supplier',
                            'project' => $delivery->project ? $delivery->project->project_name : 'Unknown'
                        ]
                    ];
                });

            // Get Material Transfer Requests (where this project is the receiver, excluding those with closed returns)
            $materialTransfers = \App\Models\Warehouse\MaterialTransferRequest::with(['transferrerProject', 'receiverProject'])
                ->where('receiver_project_id', $projectId)
                ->whereIn('status', ['approved', 'partially_approved'])
                ->get(); // Simplified - removed complex conditions for now

            \Log::info('Found material transfers', ['count' => $materialTransfers->count()]);

            $materialTransfers = $materialTransfers->map(function($transfer) {
                    return [
                        'id' => 'MT_' . $transfer->id,
                        'reference_id' => $transfer->id,
                        'reference_type' => 'material_transfer',
                        'display_text' => 'MT: ' . $transfer->transfer_request_number . ' (' . $transfer->request_date->format('M d, Y') . ') from ' . ($transfer->transferrerProject->project_name ?? 'Unknown'),
                        'sort_date' => $transfer->request_date,
                        'details' => [
                            'number' => $transfer->transfer_request_number,
                            'date' => $transfer->request_date->format('M d, Y'),
                            'from_project' => $transfer->transferrerProject ? $transfer->transferrerProject->project_name : 'Unknown',
                            'to_project' => $transfer->receiverProject ? $transfer->receiverProject->project_name : 'Unknown'
                        ]
                    ];
                });

            // Get Supplier Deliveries made directly to project site
            $supplierDeliveries = IncomingOperation::with(['supplier'])
                ->where('operation_type', 'supplier_delivery')
                ->where('delivery_type', 'project_site')
                ->where('project_id', $projectId)
                ->where('status', 'completed')
                ->get();

            \Log::info('Found supplier deliveries', ['count' => $supplierDeliveries->count()]);

            $supplierDeliveries = $supplierDeliveries->map(function($delivery) {
                return [
                    'id' => 'SD_' . $delivery->id,
                    'reference_id' => $delivery->id,
                    'reference_type' => 'supplier_delivery',
                    'display_text' => 'SD: ' . $delivery->operation_number . ' (' . $delivery->operation_date->format('M d, Y') . ') - ' . ($delivery->supplier->name ?? 'Unknown Supplier'),
                    'sort_date' => $delivery->operation_date,
                    'details' => [
                        'number' => $delivery->operation_number,
                        'date' => $delivery->operation_date->format('M d, Y'),
                        'supplier' => $delivery->supplier ? $delivery->supplier->name : 'Unknown Supplier',
                        'project' => $delivery->project ? $delivery->project->project_name : 'Unknown'
                    ]
                ];
            });

            // Get FOC Transfers - items issued from FOC inventory to this project
            $focTransfers = \App\Models\Warehouse\OutgoingFulfillment::with(['outgoingTransaction'])
                ->join('outgoing_transactions', 'outgoing_fulfillments.outgoing_transaction_id', '=', 'outgoing_transactions.id')
                ->where('outgoing_transactions.project_id', $projectId)
                ->where('outgoing_fulfillments.fulfillment_type', 'foc_transfer')
                ->select('outgoing_fulfillments.*')
                ->get()
                ->groupBy('outgoing_transaction_id');

            \Log::info('Found FOC transfers', ['count' => $focTransfers->count()]);

            $focTransfers = $focTransfers->map(function($fulfillments, $transactionId) {
                $transaction = $fulfillments->first()->outgoingTransaction;
                return [
                    'id' => 'FOC_' . $transactionId,
                    'reference_id' => $transactionId,
                    'reference_type' => 'foc_transfer',
                    'display_text' => 'FOC: ' . ($transaction->material_request_number ?: 'OUT-' . str_pad($transactionId, 6, '0', STR_PAD_LEFT)) . ' (' . $transaction->transaction_date->format('M d, Y') . ') - FOC Transfer',
                    'sort_date' => $transaction->transaction_date,
                    'details' => [
                        'number' => $transaction->material_request_number ?: 'OUT-' . str_pad($transactionId, 6, '0', STR_PAD_LEFT),
                        'date' => $transaction->transaction_date->format('M d, Y'),
                        'type' => 'FOC Transfer',
                        'project' => $transaction->project ? $transaction->project->project_name : 'Unknown'
                    ]
                ];
            })->values();

            // Combine all collections - ensure they are collections
            $combinedReferences = collect($materialRequests)
                ->merge(collect($directDeliveries))
                ->merge(collect($materialTransfers))
                ->merge(collect($supplierDeliveries))
                ->merge(collect($focTransfers));

            // Sort by date in descending order (newest first)
            $combinedReferences = $combinedReferences->sortByDesc(function ($item) {
                return $item['sort_date'];
            })->values();

            \Log::info('Combined references result', ['total_count' => $combinedReferences->count()]);

            return response()->json([
                'success' => true,
                'data' => $combinedReferences
            ]);

        } catch (\Exception $e) {
            \Log::error('Error in getCombinedReferencesByProject', [
                'project_id' => $projectId,
                'error' => $e->getMessage()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Error loading references for this project: ' . $e->getMessage(),
                'data' => []
            ]);
        }
    }

    /**
     * Get all returnable items for a project (consolidated view)
     * This allows users to return items from multiple references at once
     */
    public function getAllReturnableItems($projectId)
    {
        try {
            \Log::info('getAllReturnableItems called', ['project_id' => $projectId]);

            $allItems = [];

            // 1. Get items from Material Requests
            $materialRequests = OutgoingTransaction::with(['items.item.unit', 'project'])
                ->where('project_id', $projectId)
                ->where('status', 'successful')
                ->get();

            foreach ($materialRequests as $request) {
                $materialRequestNumber = $request->material_request_number ?: 'OUT-' . str_pad($request->id, 6, '0', STR_PAD_LEFT);

                foreach ($request->items as $item) {
                    if (!$item->item) continue;

                    $quantityReleased = $item->quantity_released ?? $item->quantity_issued ?? 0;
                    if ($quantityReleased <= 0) continue;

                    // Calculate previously returned quantity (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                        $q->where('operation_type', 'site_return')
                          ->where('project_id', $projectId)
                          ->where('status', 'completed');
                    })
                    ->where('outgoing_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate transferred to other projects
                    $transferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'outgoing_item')
                        ->where('source_id', $item->id)
                        ->sum('quantity_transferred');

                    $remainingReturnable = $quantityReleased - $previouslyReturned - $transferredToProjects;

                    if ($remainingReturnable > 0) {
                        $allItems[] = [
                            'item_id' => $item->item_id,
                            'item_description' => $item->item->item_description ?? 'Unknown Item',
                            'item_code' => $item->item->item_code ?? 'N/A',
                            'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                            'reference_type' => 'material_request',
                            'reference_id' => $request->id,
                            'reference_item_id' => $item->id,
                            'reference_number' => $materialRequestNumber,
                            'reference_date' => $request->transaction_date->format('M d, Y'),
                            'quantity_issued' => $quantityReleased,
                            'previously_returned' => $previouslyReturned,
                            'transferred_to_projects' => $transferredToProjects,
                            'remaining_returnable' => $remainingReturnable,
                            'unit_price' => $item->unit_price ?? 0,
                            'division_name' => $item->division_name ?? null,
                        ];
                    }
                }
            }

            // 2. Get items from Direct Deliveries
            $directDeliveries = \App\Models\DirectDelivery::with(['items.item.unit', 'project'])
                ->where('project_id', $projectId)
                ->whereIn('status', ['delivered', 'completed'])
                ->get();

            foreach ($directDeliveries as $delivery) {
                foreach ($delivery->items as $item) {
                    if (!$item->item) continue;

                    $deliveredQuantity = $item->delivered_quantity ?? 0;
                    if ($deliveredQuantity <= 0) continue;

                    // Calculate previously returned quantity (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                        $q->where('operation_type', 'site_return')
                          ->where('project_id', $projectId)
                          ->where('status', 'completed');
                    })
                    ->where('direct_delivery_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate transferred to other projects
                    $transferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'direct_delivery_item')
                        ->where('source_id', $item->id)
                        ->sum('quantity_transferred');

                    $remainingReturnable = $deliveredQuantity - $previouslyReturned - $transferredToProjects;

                    if ($remainingReturnable > 0) {
                        $allItems[] = [
                            'item_id' => $item->item_id,
                            'item_description' => $item->item->item_description ?? 'Unknown Item',
                            'item_code' => $item->item->item_code ?? 'N/A',
                            'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                            'reference_type' => 'direct_delivery',
                            'reference_id' => $delivery->id,
                            'reference_item_id' => $item->id,
                            'reference_number' => $delivery->delivery_number,
                            'reference_date' => $delivery->delivery_date->format('M d, Y'),
                            'quantity_issued' => $deliveredQuantity,
                            'previously_returned' => $previouslyReturned,
                            'transferred_to_projects' => $transferredToProjects,
                            'remaining_returnable' => $remainingReturnable,
                            'unit_price' => $item->unit_cost ?? $item->unit_price ?? 0,
                            'division_name' => null,
                        ];
                    }
                }
            }

            // 3. Get items from Supplier Deliveries (direct to project)
            $supplierDeliveries = IncomingOperation::with(['items.item.unit'])
                ->where('operation_type', 'supplier_delivery')
                ->where('delivery_type', 'project_site')
                ->where('project_id', $projectId)
                ->where('status', 'completed')
                ->get();

            foreach ($supplierDeliveries as $delivery) {
                foreach ($delivery->items as $item) {
                    if (!$item->item) continue;

                    $deliveredQuantity = max($item->quantity_delivered, $item->quantity_accepted) ?? 0;
                    if ($deliveredQuantity <= 0) continue;

                    // Calculate previously returned quantity (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                        $q->where('operation_type', 'site_return')
                          ->where('project_id', $projectId)
                          ->where('status', 'completed');
                    })
                    ->where('supplier_delivery_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate transferred to other projects
                    $transferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'supplier_delivery_item')
                        ->where('source_id', $item->id)
                        ->sum('quantity_transferred');

                    $remainingReturnable = $deliveredQuantity - $previouslyReturned - $transferredToProjects;

                    if ($remainingReturnable > 0) {
                        $allItems[] = [
                            'item_id' => $item->item_id,
                            'item_description' => $item->item->item_description ?? 'Unknown Item',
                            'item_code' => $item->item->item_code ?? 'N/A',
                            'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                            'reference_type' => 'supplier_delivery',
                            'reference_id' => $delivery->id,
                            'reference_item_id' => $item->id,
                            'reference_number' => $delivery->operation_number,
                            'reference_date' => $delivery->operation_date->format('M d, Y'),
                            'quantity_issued' => $deliveredQuantity,
                            'previously_returned' => $previouslyReturned,
                            'transferred_to_projects' => $transferredToProjects,
                            'remaining_returnable' => $remainingReturnable,
                            'unit_price' => $item->unit_price_aed ?? $item->unit_price ?? 0,
                            'division_name' => null,
                        ];
                    }
                }
            }

            // 4. Get items from Material Transfers (site-to-site)
            $siteTransfers = \App\Models\Warehouse\MaterialSiteTransfer::with(['items.item.unit'])
                ->where('to_project_id', $projectId)
                ->where('status', 'completed')
                ->get();

            foreach ($siteTransfers as $transfer) {
                foreach ($transfer->items as $item) {
                    if (!$item->item) continue;

                    $transferredQuantity = $item->quantity_transferred ?? 0;
                    if ($transferredQuantity <= 0) continue;

                    // Calculate previously returned quantity (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                        $q->where('operation_type', 'site_return')
                          ->where('project_id', $projectId)
                          ->where('status', 'completed');
                    })
                    ->where('material_transfer_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate transferred to other projects
                    $transferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'site_transfer_item')
                        ->where('source_id', $item->id)
                        ->sum('quantity_transferred');

                    $remainingReturnable = $transferredQuantity - $previouslyReturned - $transferredToProjects;

                    if ($remainingReturnable > 0) {
                        $allItems[] = [
                            'item_id' => $item->item_id,
                            'item_description' => $item->item->item_description ?? 'Unknown Item',
                            'item_code' => $item->item->item_code ?? 'N/A',
                            'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                            'reference_type' => 'site_transfer',
                            'reference_id' => $transfer->id,
                            'reference_item_id' => $item->id,
                            'reference_number' => 'ST-' . str_pad($transfer->id, 5, '0', STR_PAD_LEFT),
                            'reference_date' => $transfer->transfer_date->format('M d, Y'),
                            'quantity_issued' => $transferredQuantity,
                            'previously_returned' => $previouslyReturned,
                            'transferred_to_projects' => $transferredToProjects,
                            'remaining_returnable' => $remainingReturnable,
                            'unit_price' => $item->unit_price ?? 0,
                            'division_name' => null,
                        ];
                    }
                }
            }

            // Sort by item description for easier viewing
            usort($allItems, function($a, $b) {
                return strcmp($a['item_description'], $b['item_description']);
            });

            \Log::info('getAllReturnableItems result', ['total_items' => count($allItems)]);

            return response()->json([
                'success' => true,
                'data' => [
                    'items' => $allItems,
                    'summary' => [
                        'total_items' => count($allItems),
                        'material_requests' => count(array_filter($allItems, fn($i) => $i['reference_type'] === 'material_request')),
                        'direct_deliveries' => count(array_filter($allItems, fn($i) => $i['reference_type'] === 'direct_delivery')),
                        'supplier_deliveries' => count(array_filter($allItems, fn($i) => $i['reference_type'] === 'supplier_delivery')),
                        'site_transfers' => count(array_filter($allItems, fn($i) => $i['reference_type'] === 'site_transfer')),
                    ]
                ]
            ]);

        } catch (\Exception $e) {
            \Log::error('Error in getAllReturnableItems', [
                'project_id' => $projectId,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Error loading returnable items: ' . $e->getMessage(),
                'data' => []
            ]);
        }
    }

    /**
     * Get material transfer details for site return
     */
    public function getMaterialTransfer($transferId)
    {
        try {
            $materialTransfer = \App\Models\Warehouse\MaterialTransferRequest::with([
                'transferrerProject',
                'receiverProject',
                'items.item'
            ])->findOrFail($transferId);

            // Only allow returns for approved/partially approved transfers
            if (!in_array($materialTransfer->status, ['approved', 'partially_approved'])) {
                return response()->json([
                    'success' => false,
                    'message' => 'Material transfer must be approved to be available for return.'
                ]);
            }

            $data = [
                'id' => $materialTransfer->id,
                'transfer_request_number' => $materialTransfer->transfer_request_number,
                'request_date' => $materialTransfer->request_date->format('M d, Y'),
                'transferrer_project_id' => $materialTransfer->transferrer_project_id,
                'transferrer_project_name' => $materialTransfer->transferrerProject ? $materialTransfer->transferrerProject->project_name : 'Unknown Project',
                'receiver_project_id' => $materialTransfer->receiver_project_id,
                'receiver_project_name' => $materialTransfer->receiverProject ? $materialTransfer->receiverProject->project_name : 'Unknown Project',
                'status' => ucwords(str_replace('_', ' ', $materialTransfer->status)),
                'priority' => ucfirst($materialTransfer->priority),
                'items' => $materialTransfer->items->map(function($item) use ($materialTransfer) {
                    // For material transfers, we need to check what was actually approved/transferred
                    $transferredQuantity = $item->quantity_approved ?? 0;

                    // Calculate already returned quantity for this transfer item (good + damaged)
                    $returnedQuantity = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($query) {
                        $query->where('operation_type', 'site_return')
                              ->where('status', 'completed');
                    })
                    ->where('material_transfer_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate quantity transferred to other projects
                    // Check for subsequent transfers from this Material Transfer item using the new tracking field
                    $transferredToOtherProjects = \App\Models\Warehouse\MaterialTransferRequestItem::whereHas('transferRequest', function($query) {
                        $query->whereIn('status', ['approved', 'partially_approved']);
                    })
                    ->where('material_transfer_item_id', $item->id)
                    ->sum('quantity_approved');

                    // Enhanced calculation: if no specific tracking found, try to calculate based on recent transfer requests
                    if ($transferredToOtherProjects == 0) {
                        // Look for Material Transfer Requests where items came from this receiver project
                        // and check if they reference items that might have originated from this transfer
                        $subsequentTransfers = \App\Models\Warehouse\MaterialTransferRequestItem::whereHas('transferRequest', function($query) use ($materialTransfer) {
                            $query->where('transferrer_project_id', $materialTransfer->receiver_project_id)
                                  ->whereIn('status', ['approved', 'partially_approved']);
                        })
                        ->where('item_id', $item->item_id)
                        ->whereDate('created_at', '>=', $materialTransfer->approved_at ?? $materialTransfer->created_at)
                        ->sum('quantity_approved');

                        $transferredToOtherProjects = $subsequentTransfers;
                    }

                    \Log::info('Material Transfer calculation', [
                        'transfer_id' => $materialTransfer->id,
                        'item_id' => $item->item_id,
                        'receiver_project_id' => $materialTransfer->receiver_project_id,
                        'transferred_quantity' => $transferredQuantity,
                        'returned_quantity' => $returnedQuantity,
                        'transferred_to_projects' => $transferredToOtherProjects,
                        'calculation_method' => $transferredToOtherProjects > 0 ? 'direct_tracking' : 'subsequent_transfers'
                    ]);

                    // Calculate remaining returnable
                    $remainingReturnable = max(0, $transferredQuantity - $returnedQuantity - $transferredToOtherProjects);

                    return [
                        'id' => $item->id,
                        'item_id' => $item->item_id,
                        'item_code' => $item->item ? $item->item->item_code : 'N/A',
                        'item_description' => $item->item ? $item->item->item_description : 'Unknown Item',
                        'unit' => $item->item ? $item->item->unit_of_measure : 'PCS',
                        'division_name' => 'No Division', // Material transfers don't have item-level divisions
                        'quantity_requested' => $item->quantity_requested,
                        'quantity_approved' => $transferredQuantity,
                        'previously_returned' => $returnedQuantity,
                        'transferred_to_projects' => $transferredToOtherProjects,
                        'remaining_returnable' => $remainingReturnable,
                        'estimated_unit_price' => $item->estimated_unit_price ?? 0,
                        'approval_status' => $item->approval_status,
                    ];
                })->filter(function($item) {
                    // Include items that have been transferred (have approved quantity > 0)
                    // For partially transferred materials, we need any item that was actually transferred
                    return $item['quantity_approved'] > 0;
                })->values()
            ];

            return response()->json([
                'success' => true,
                'data' => $data
            ]);

        } catch (\Exception $e) {
            \Log::error('Error getting material transfer for site return: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Error loading material transfer details: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get supplier delivery details for site return
     */
    public function getSupplierDelivery($deliveryId)
    {
        try {
            $supplierDelivery = IncomingOperation::with([
                'supplier',
                'project',
                'division',
                'items.item'
            ])->where('operation_type', 'supplier_delivery')
              ->where('delivery_type', 'project_site')
              ->findOrFail($deliveryId);

            // Only allow returns for completed supplier deliveries
            if ($supplierDelivery->status !== 'completed') {
                return response()->json([
                    'success' => false,
                    'message' => 'Supplier delivery must be completed to be available for return.'
                ]);
            }

            \Log::info('Supplier Delivery Found', [
                'id' => $supplierDelivery->id,
                'operation_number' => $supplierDelivery->operation_number,
                'items_count' => $supplierDelivery->items->count(),
            ]);

            $data = [
                'id' => $supplierDelivery->id,
                'operation_number' => $supplierDelivery->operation_number,
                'operation_date' => $supplierDelivery->operation_date->format('M d, Y'),
                'project_id' => $supplierDelivery->project_id,
                'project_name' => $supplierDelivery->project ? $supplierDelivery->project->project_name : 'Unknown Project',
                'supplier_name' => $supplierDelivery->supplier ? $supplierDelivery->supplier->name : 'Unknown Supplier',
                'lpo_number' => $supplierDelivery->lpo_number,
                'status' => ucwords(str_replace('_', ' ', $supplierDelivery->status)),
                'items' => $supplierDelivery->items->map(function($item) use ($supplierDelivery) {
                    // For supplier deliveries, we need the delivered quantity as returnable
                    $deliveredQuantity = $item->quantity_delivered ?? 0;

                    // Calculate already returned quantity for this supplier delivery item (good + damaged)
                    $returnedQuantity = IncomingOperationItem::where('supplier_delivery_item_id', $item->id)
                        ->whereHas('operation', function($query) {
                            $query->where('operation_type', 'site_return')
                                  ->where('status', 'completed');
                        })
                        ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                        ->value('total') ?? 0;

                    // Calculate remaining returnable
                    $remainingReturnable = $deliveredQuantity - $returnedQuantity;

                    // Get division name - use operation-level division since supplier delivery items don't have individual divisions
                    $divisionName = 'No Division';
                    if ($supplierDelivery->division_id) {
                        $division = $supplierDelivery->relationLoaded('division')
                            ? $supplierDelivery->getRelation('division')
                            : $supplierDelivery->division;
                        $divisionName = $division ? $division->division_name : 'No Division';
                    }

                    return [
                        'id' => $item->id,
                        'item_id' => $item->item_id,
                        'item_code' => $item->item ? $item->item->item_code : 'N/A',
                        'item_description' => $item->item ? $item->item->item_description : 'Unknown Item',
                        'unit' => $item->item ? $item->item->unit_of_measure : 'PCS',
                        'division_name' => $divisionName,
                        'quantity_delivered' => $deliveredQuantity,
                        'previously_returned' => $returnedQuantity,
                        'remaining_returnable' => $remainingReturnable,
                        'unit_price' => $item->unit_price ?? 0,
                    ];
                })->filter(function($item) {
                    // Include items that have been delivered (temporarily remove returnable filter for debugging)
                    return $item['quantity_delivered'] > 0;
                })->values()
            ];

            \Log::info('Supplier Delivery Items Processed', [
                'items_count' => count($data['items']),
                'items_data' => $data['items']
            ]);

            return response()->json([
                'success' => true,
                'data' => $data
            ]);

        } catch (\Exception $e) {
            \Log::error('Error getting supplier delivery for site return: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Error loading supplier delivery details: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get FOC transfer details for site return
     */
    public function getFocTransfer($transactionId)
    {
        try {
            $transaction = \App\Models\Warehouse\OutgoingTransaction::with([
                'project',
                'requester',
                'items.item'
            ])->findOrFail($transactionId);

            // Get FOC fulfillments for this transaction
            $focFulfillments = \App\Models\Warehouse\OutgoingFulfillment::where('outgoing_transaction_id', $transactionId)
                ->where('fulfillment_type', 'foc_transfer')
                ->get()
                ->groupBy('item_id');

            if ($focFulfillments->isEmpty()) {
                return response()->json([
                    'success' => false,
                    'message' => 'No FOC transfers found for this transaction.'
                ]);
            }

            // Only allow returns for successful transactions
            if ($transaction->status !== 'successful') {
                return response()->json([
                    'success' => false,
                    'message' => 'Transaction must be successful to be available for FOC return.'
                ]);
            }

            \Log::info('FOC Transfer Found', [
                'id' => $transaction->id,
                'material_request_number' => $transaction->material_request_number,
                'foc_items_count' => $focFulfillments->count(),
            ]);

            $data = [
                'id' => $transaction->id,
                'material_request_number' => $transaction->material_request_number ?: 'OUT-' . str_pad($transaction->id, 6, '0', STR_PAD_LEFT),
                'transaction_date' => $transaction->transaction_date->format('M d, Y'),
                'project_id' => $transaction->project_id,
                'project_name' => $transaction->project ? $transaction->project->project_name : 'Unknown Project',
                'requester_name' => $transaction->requester ? $transaction->requester->name : 'Unknown Requester',
                'status' => ucwords(str_replace('_', ' ', $transaction->status)),
                'total_foc_items' => $focFulfillments->count(),
                'items' => $focFulfillments->map(function($fulfillments, $itemId) use ($transaction) {
                    $item = $fulfillments->first();
                    $focTransferredQuantity = $fulfillments->sum('quantity_fulfilled');

                    // Calculate already returned quantity for this FOC item (good + damaged)
                    $returnedQuantity = \App\Models\Warehouse\IncomingOperationItem::where('outgoing_fulfillment_id', $item->id)
                        ->whereHas('operation', function($query) {
                            $query->where('operation_type', 'site_return')
                                  ->where('status', 'completed');
                        })
                        ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                        ->value('total') ?? 0;

                    // Calculate remaining returnable
                    $remainingReturnable = $focTransferredQuantity - $returnedQuantity;

                    // Get item details
                    $itemDetails = \App\Models\Warehouse\Item::find($itemId);

                    return [
                        'id' => $item->id,
                        'item_id' => $itemId,
                        'item_code' => $itemDetails ? $itemDetails->item_code : 'N/A',
                        'item_description' => $itemDetails ? $itemDetails->item_description : 'Unknown Item',
                        'unit' => $itemDetails ? $itemDetails->unit_of_measure : 'PCS',
                        'quantity_transferred' => $focTransferredQuantity,
                        'previously_returned' => $returnedQuantity,
                        'remaining_returnable' => $remainingReturnable,
                        'unit_price' => 0, // FOC items have no cost
                        'is_foc' => true, // Flag to identify FOC items
                    ];
                })->filter(function($item) {
                    // Include items that have been transferred and have remaining returnable quantity
                    return $item['remaining_returnable'] > 0;
                })->values()
            ];

            \Log::info('FOC Transfer Items Processed', [
                'items_count' => count($data['items']),
                'items_data' => $data['items']
            ]);

            return response()->json([
                'success' => true,
                'data' => $data
            ]);

        } catch (\Exception $e) {
            \Log::error('Error getting FOC transfer for site return: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Error loading FOC transfer details: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get site return data for AJAX
     */
    public function siteReturnData(Request $request)
    {
        $query = IncomingOperation::with(['project', 'items', 'receiver'])
                                 ->where('operation_type', 'site_return');

        // Apply filters if provided
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        // Additional filters can be added here

        $returns = $query->latest('operation_date')->get();

        return response()->json([
            'data' => $returns,
            'recordsTotal' => $returns->count(),
            'recordsFiltered' => $returns->count()
        ]);
    }

    /**
     * Export site returns
     */
    public function exportSiteReturns(Request $request)
    {
        // This is a placeholder for export functionality
        // You would implement actual export logic here (Excel, CSV, etc.)

        return response()->json([
            'success' => false,
            'message' => 'Export functionality will be implemented soon'
        ]);
    }

    /**
     * Calculate site return status based on return quantities vs issued quantities
     */
    private function calculateSiteReturnStatus($operation, $request)
    {
        // If force close is enabled, return force_closed status
        if ($operation->shouldForceClose()) {
            return 'force_closed';
        }

        $totalIssued = 0;
        $totalReturned = 0;

        // Check if this is a FOC return
        $isFocReturn = false;
        foreach ($request->items as $item) {
            if (isset($item['is_foc']) && $item['is_foc'] == '1') {
                $isFocReturn = true;
                break;
            }
        }

        // For FOC returns, use total_qty_to_return
        if ($isFocReturn) {
            foreach ($request->items as $returnItem) {
                $totalReturned += (float)($returnItem['total_qty_to_return'] ?? 0);
            }
            // FOC returns don't have issued quantities to compare, so return a special status
            return $totalReturned > 0 ? 'foc_returned' : 'not_returned';
        }

        // Get the material request to compare against issued quantities
        $materialRequestId = $request->material_request_id;
        $materialRequest = \App\Models\Warehouse\OutgoingTransaction::with('items')->find($materialRequestId);

        if (!$materialRequest) {
            return 'not_returned';
        }

        // Calculate totals
        foreach ($materialRequest->items as $issuedItem) {
            $totalIssued += (float)$issuedItem->quantity_released;

            // Find the corresponding returned item
            foreach ($request->items as $returnItem) {
                if (isset($returnItem['outgoing_item_id']) && $returnItem['outgoing_item_id'] == $issuedItem->id) {
                    $totalReturned += (float)($returnItem['quantity_returned'] ?? 0);
                    break;
                }
            }
        }

        // Determine status based on return ratio
        if ($totalReturned == 0) {
            return 'not_returned';
        } elseif ($totalReturned >= $totalIssued) {
            return 'closed_return';  // All or more returned
        } else {
            return 'partially_returned';  // Some returned
        }
    }

    /**
     * Print material request returns
     */
    public function printMaterialRequestReturns($materialRequestId)
    {
        Gate::authorize('viewAny', \App\Models\Warehouse\Inventory::class);

        // Get the material request (outgoing transaction)
        $materialRequest = OutgoingTransaction::with(['project', 'requester', 'items.item'])
                                             ->findOrFail($materialRequestId);

        // Get all site returns for this material request
        $returns = IncomingOperation::with(['items.item', 'receiver'])
                                  ->where('operation_type', 'site_return')
                                  ->where('material_request_id', $materialRequestId)
                                  ->orderBy('operation_date', 'desc')
                                  ->get();

        // Prepare data for print template
        $siteReturn = [
            'id' => $materialRequest->id,
            'return_number' => 'SR-' . $materialRequest->material_request_number,
            'return_date' => $returns->isNotEmpty() ? $returns->first()->operation_date->format('d.m.Y') : now()->format('d.m.Y'),
            'project' => $materialRequest->project ? ($materialRequest->project->project_number . ' - ' . $materialRequest->project->project_name) : 'N/A',
            'returned_to' => 'Warehouse',
            'returned_by' => $returns->isNotEmpty() ? ($returns->first()->receiver->name ?? 'N/A') : 'N/A',
            'items' => $returns->flatMap(function($return) {
                return $return->items->map(function($item) {
                    return [
                        'description' => $item->item->item_description,
                        'unit' => $item->item->unit_of_measure ?: 'PCS',
                        'quantity_returned' => $item->quantity_delivered
                    ];
                });
            })->toArray()
        ];

        return view('warehouse.site-return.print', compact('siteReturn'));
    }

    /**
     * Print individual site return
     */
    public function printSiteReturn($returnId)
    {
        Gate::authorize('viewAny', \App\Models\Warehouse\Inventory::class);

        // Get the site return operation
        $return = IncomingOperation::with(['project', 'receiver', 'items.item', 'materialRequest'])
                                  ->where('operation_type', 'site_return')
                                  ->findOrFail($returnId);

        // Prepare data for print template
        $siteReturn = [
            'id' => $return->id,
            'return_number' => $return->operation_number ?: 'SR-' . $return->id,
            'return_date' => $return->operation_date->format('d.m.Y'),
            'project' => $return->project ? ($return->project->project_number . ' - ' . $return->project->project_name) : 'N/A',
            'returned_to' => 'Warehouse',
            'returned_by' => $return->receiver ? $return->receiver->name : 'N/A',
            'items' => $return->items->map(function($item) {
                return [
                    'description' => $item->item->item_description,
                    'unit' => $item->item->unit_of_measure ?: 'PCS',
                    'quantity_returned' => $item->quantity_delivered
                ];
            })->toArray()
        ];

        return view('warehouse.site-return.print', compact('siteReturn'));
    }

    /**
     * Print consolidated returns for a project.
     */
    public function printProjectConsolidatedReturns($projectId)
    {
        // Handle unassigned returns (projectId = 0)
        if ($projectId == 0) {
            $project = (object) [
                'id' => 0,
                'project_name' => 'Unassigned Returns',
                'project_number' => 'N/A',
                'projectDivision' => null
            ];
        } else {
            // Get the project
            $project = Project::findOrFail($projectId);
        }

        // Get all site returns for this project with related data
        $query = IncomingOperation::with([
            'items.item',
            'materialRequest',
            'directDelivery',
            'receiver'  // Changed from receivedByUser
        ])
        ->where('operation_type', 'site_return');

        // Handle unassigned returns
        if ($projectId == 0) {
            $query->whereNull('project_id');
        } else {
            $query->where('project_id', $projectId);
        }

        $returns = $query->orderBy('operation_date', 'desc')->get();

        // Group returns by material request for better organization
        $groupedReturns = $returns->groupBy('material_request_id');

        // Calculate totals
        $totalItems = 0;
        $totalValue = 0;
        $itemsSummary = [];

        foreach ($returns as $return) {
            foreach ($return->items as $item) {
                $totalItems += $item->quantity_delivered;
                $totalValue += ($item->quantity_delivered * $item->unit_price);

                // Build items summary
                $itemCode = $item->item->item_code ?? 'N/A';
                if (!isset($itemsSummary[$itemCode])) {
                    $itemsSummary[$itemCode] = [
                        'code' => $itemCode,
                        'description' => $item->item->item_description ?? 'N/A',
                        'unit' => $item->item->unit_of_measure ?? 'pcs',
                        'total_quantity' => 0,
                        'returns' => []
                    ];
                }
                $itemsSummary[$itemCode]['total_quantity'] += $item->quantity_delivered;
                $itemsSummary[$itemCode]['returns'][] = [
                    'date' => $return->operation_date->format('d/m/Y'),
                    'mr_number' => $return->materialRequest->mr_number ?? 'Direct',
                    'quantity' => $item->quantity_delivered,
                    'condition' => $item->return_condition ?? 'Good'
                ];
            }
        }

        // Prepare consolidated data
        $consolidatedData = [
            'project' => $project,
            'returns' => $returns,
            'groupedReturns' => $groupedReturns,
            'itemsSummary' => $itemsSummary,
            'totalReturns' => $returns->count(),
            'totalItems' => $totalItems,
            'totalValue' => $totalValue,
            'printDate' => now()->format('d/m/Y H:i'),
            'printedBy' => auth()->user()->name ?? 'System'
        ];

        return view('warehouse.site-return.print-consolidated', compact('consolidatedData'));
    }

    /**
     * Create damaged item entry for site returns
     */
    private function createDamagedItemEntry($operation, $incomingItem, $itemData, $damagedQty, $request)
    {
        // Get item details for cost calculation
        $item = Item::find($itemData['item_id']);
        if (!$item) {
            return;
        }

        // Calculate unit cost - prioritize the price from the incoming item (which has the correct price)
        // Fall back to unit_price from form data, then to inventory average cost
        $unitCost = $incomingItem->unit_price_aed ?? 0;
        if (!$unitCost || $unitCost == 0) {
            $unitCost = $itemData['unit_price'] ?? 0;
        }
        if (!$unitCost || $unitCost == 0) {
            $unitCost = $item->inventory_valuation_average_cost ?? 0;
        }
        $totalCost = $unitCost * $damagedQty;

        // Determine cost responsibility based on return reason and damage status
        // Since this method is only called for damaged items, we pass true for isDamaged
        $costResponsibility = $this->determineCostResponsibility($itemData['return_reason'] ?? 'other', true);

        // Create damaged item entry
        $damagedItemData = [
            'item_id' => $itemData['item_id'],
            'quantity' => $damagedQty,
            'unit' => $item->unit_of_measure,
            'unit_cost' => $unitCost,
            'total_cost' => $totalCost,
            'source_type' => 'site_return',
            'source_reference' => $operation->operation_number,
            'project_id' => $operation->project_id,
            'project_details' => $operation->project->project_name ?? null,
            'damage_description' => $this->generateDamageDescription($itemData),
            'damage_severity' => $this->determineDamageSeverity($itemData['condition'] ?? 'damaged'),
            'damage_notes' => $itemData['notes'] ?? null,
            'cost_responsibility' => $costResponsibility,
            'status' => 'reported',
            'recoverable_amount' => 0, // Initially set to 0, can be updated later
            'loss_amount' => $totalCost,
            'reported_by' => auth()->id(),
            'reported_at' => now(),
        ];

        $damagedItem = DamagedItem::create($damagedItemData);

        // Also create FOC inventory entry for immediate use
        $this->createFocInventoryEntry($operation, $damagedItem, $itemData, $damagedQty);

        \Log::info('Damaged item and FOC inventory created for site return', [
            'operation_id' => $operation->id,
            'item_id' => $itemData['item_id'],
            'damaged_qty' => $damagedQty,
            'total_cost' => $totalCost
        ]);
    }

    /**
     * Create FOC inventory entry from damaged item
     */
    private function createFocInventoryEntry($operation, $damagedItem, $itemData, $quantity)
    {
        // Get item details
        $item = \App\Models\Warehouse\Item::find($itemData['item_id']);
        if (!$item) {
            return;
        }

        // Map return condition to FOC inventory condition
        $focCondition = $this->mapConditionForFocInventory($itemData['condition'] ?? 'damaged');

        // Determine damage severity
        $damageSeverity = $this->determineDamageSeverity($itemData['condition'] ?? 'damaged');

        // Create FOC inventory entry
        $focInventoryData = [
            'item_id' => $itemData['item_id'],
            'quantity_available' => $quantity,
            'quantity_reserved' => 0,
            'unit' => $item->unit_of_measure,
            'source_type' => 'site_return',
            'source_reference' => $operation->operation_number,
            'source_damaged_item_id' => $damagedItem->id,
            'source_project_id' => $operation->project_id,
            'condition' => $focCondition,
            'condition_notes' => $itemData['notes'] ?? null,
            'damage_severity' => $damageSeverity,
            'status' => 'available',
            'quality_status' => 'approved', // Pre-approve for FOC use
            'estimated_value' => $damagedItem->total_cost,
            'recoverable_value' => $damagedItem->total_cost * 0.5, // Assume 50% recovery value
            'received_at' => now(),
            'created_by' => auth()->id(),
        ];

        \App\Models\FocInventory::create($focInventoryData);

        \Log::info('FOC inventory entry created', [
            'operation_id' => $operation->id,
            'item_id' => $itemData['item_id'],
            'quantity' => $quantity,
            'condition' => $focCondition
        ]);
    }

    /**
     * Map return condition to FOC inventory condition
     */
    private function mapConditionForFocInventory($returnCondition)
    {
        $conditionMap = [
            'damaged' => 'damaged',
            'excess' => 'excess',
            'unused' => 'unused',
            'good' => 'serviceable',
            'serviceable' => 'serviceable'
        ];

        return $conditionMap[$returnCondition] ?? 'damaged';
    }

    /**
     * Determine cost responsibility based on return reason
     * For damaged items, cost responsibility is always assigned to the project
     */
    private function determineCostResponsibility($returnReason, $isDamaged = false)
    {
        // If item is damaged, cost responsibility should always be assigned to the project
        if ($isDamaged) {
            return 'project';
        }

        $responsibilityMap = [
            'damaged_items' => 'project',
            'wrong_specification' => 'company',
            'quality_issues' => 'supplier',
            'project_completion' => 'company',
            'excess_materials' => 'company',
            'other' => 'pending'
        ];

        return $responsibilityMap[$returnReason] ?? 'pending';
    }

    /**
     * Generate damage description based on item data
     */
    private function generateDamageDescription($itemData)
    {
        $description = 'Damaged item returned from site';

        if (!empty($itemData['return_reason'])) {
            $description .= ' - Reason: ' . str_replace('_', ' ', ucwords($itemData['return_reason']));
        }

        if (!empty($itemData['condition'])) {
            $description .= ' - Condition: ' . ucwords($itemData['condition']);
        }

        return $description;
    }

    /**
     * Determine damage severity based on condition
     */
    private function determineDamageSeverity($condition)
    {
        $severityMap = [
            'good' => 'minor',
            'fair' => 'moderate',
            'damaged' => 'major',
            'unusable' => 'total_loss'
        ];

        return $severityMap[$condition] ?? 'moderate';
    }

    /**
     * Process site return to inventory - only returnable quantities
     */
    private function processSiteReturnToInventory($operation, $request)
    {
        \Log::info('Processing site return to inventory', ['operation_id' => $operation->id]);

        $isConsolidatedMode = $request->reference_type === 'consolidated';

        foreach ($operation->items as $item) {
            // For consolidated mode, each IncomingOperationItem already has the correct quantity from allocations
            if ($isConsolidatedMode) {
                $returnableQty = $item->quantity_delivered; // Already set during item creation
                $damagedQty = $item->quantity_rejected ?? 0; // Damaged items tracked in quantity_rejected

                if ($returnableQty > 0 || $damagedQty > 0) {
                    // Determine the division_id and check if FOC from the original source
                    $divisionId = null;
                    $isFocReturn = false;
                    $sourceType = $this->determineSourceTypeFromItem($item);

                    // Check if this is a FOC transfer return (source_type = 'foc_transfer')
                    // FOC transfers are stored with a special source type
                    if ($sourceType === 'foc_transfer') {
                        $isFocReturn = true;
                    }

                    // Get division from source based on source type
                    if ($item->outgoing_item_id) {
                        // Material Request return
                        $outgoingItem = \App\Models\Warehouse\OutgoingItem::find($item->outgoing_item_id);
                        if ($outgoingItem) {
                            $divisionId = $outgoingItem->division_id;
                            // Check if this was a FOC item
                            if (!$isFocReturn) {
                                $isFocReturn = $outgoingItem->is_foc ?? false;
                            }
                        }
                    } elseif ($item->direct_delivery_item_id) {
                        // Direct Delivery return
                        $ddItem = \App\Models\DirectDeliveryItem::find($item->direct_delivery_item_id);
                        if ($ddItem && $ddItem->directDelivery) {
                            $divisionId = $ddItem->directDelivery->project_division_id;
                        }
                    } elseif ($item->supplier_delivery_item_id) {
                        // Supplier Delivery return - division from original supplier delivery
                        $sdItem = \App\Models\Warehouse\IncomingOperationItem::find($item->supplier_delivery_item_id);
                        if ($sdItem && $sdItem->operation) {
                            $divisionId = $sdItem->operation->division_id;
                            // Check if this was an FOC supplier delivery
                            if ($sdItem->operation->delivery_type === 'foc') {
                                $isFocReturn = true;
                            }
                        }
                    } elseif ($item->material_transfer_item_id) {
                        // Site Transfer return - resolve division/FOC from the allocation
                        if ($item->site_transfer_allocation_id) {
                            // Precise allocation tracking - use the specific allocation
                            $allocation = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::find($item->site_transfer_allocation_id);
                            if ($allocation) {
                                $resolved = \App\Helpers\AllocationDivisionResolver::resolve($allocation);
                                $divisionId = $resolved['division_id'];
                                $isFocReturn = $resolved['is_foc'];
                            }
                        } else {
                            // Legacy fallback - no specific allocation_id
                            $mtItem = \App\Models\Warehouse\MaterialSiteTransferItem::find($item->material_transfer_item_id);
                            if ($mtItem) {
                                $allocations = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('site_transfer_item_id', $mtItem->id)->get();
                                if ($allocations->count() === 1) {
                                    $resolved = \App\Helpers\AllocationDivisionResolver::resolve($allocations->first());
                                    $divisionId = $resolved['division_id'];
                                    $isFocReturn = $resolved['is_foc'];
                                } elseif ($allocations->count() > 1) {
                                    // Multiple allocations without specific tracking - resolve first as fallback
                                    $resolved = \App\Helpers\AllocationDivisionResolver::resolve($allocations->first());
                                    $divisionId = $resolved['division_id'];
                                    $isFocReturn = $resolved['is_foc'];
                                }
                            }
                        }
                    } elseif ($item->outgoing_fulfillment_id) {
                        // FOC Transfer return - trace back to find the original division
                        $isFocReturn = true;

                        // Get the OutgoingFulfillment to find the original FocInventory and its division
                        $fulfillment = \App\Models\Warehouse\OutgoingFulfillment::find($item->outgoing_fulfillment_id);
                        if ($fulfillment && $fulfillment->foc_inventory_id) {
                            $originalFocInventory = \App\Models\FocInventory::find($fulfillment->foc_inventory_id);
                            if ($originalFocInventory && $originalFocInventory->division_id) {
                                $divisionId = $originalFocInventory->division_id;
                            }
                        }
                    }

                    // Handle FOC returns separately
                    if ($isFocReturn && $returnableQty > 0) {
                        $this->processFocReturnConsolidated($item, $returnableQty, $operation, $divisionId);
                        // Handle damaged FOC items too
                        if ($damagedQty > 0) {
                            $this->processDamagedReturnConsolidated($item, $damagedQty, $operation, $divisionId);
                        }
                        continue;
                    }

                    // Process regular return
                    if ($returnableQty > 0) {
                        // Set the division_id on the operation temporarily for this item processing
                        $originalDivisionId = $operation->division_id;
                        if ($divisionId) {
                            $operation->division_id = $divisionId;
                        }

                        // Create a temporary item for processing
                        $tempItem = clone $item;
                        $tempItem->quantity_accepted = $returnableQty;

                        // Process to inventory (adds to warehouse inventory, subtracts from project inventory)
                        $operation->processItemToInventory($tempItem);

                        // Restore original division_id
                        $operation->division_id = $originalDivisionId;

                        \Log::info('Processed consolidated return to inventory', [
                            'item_id' => $item->item_id,
                            'returnable_qty' => $returnableQty,
                            'damaged_qty' => $damagedQty,
                            'division_id' => $divisionId,
                            'source_type' => $sourceType,
                            'project_id' => $operation->project_id
                        ]);
                    }

                    // Handle damaged items - subtract from project but don't add to main inventory
                    // Pass division_id for proper division stock tracking
                    if ($damagedQty > 0) {
                        $this->processDamagedReturnConsolidated($item, $damagedQty, $operation, $divisionId);
                    }
                }
                continue;
            }

            // Non-consolidated mode: Find the original item data from request to get returnable quantity
            $originalItemData = null;
            foreach ($request->items as $itemData) {
                if ($itemData['item_id'] == $item->item_id) {
                    $originalItemData = $itemData;
                    break;
                }
            }

            if (!$originalItemData) {
                \Log::warning('Original item data not found', ['item_id' => $item->item_id]);
                continue;
            }

            // Check if this is a FOC return
            $isFocReturn = isset($originalItemData['is_foc']) && $originalItemData['is_foc'] == '1';

            // Also check if this is a return from a supplier FOC delivery
            if (!$isFocReturn && $item->supplier_delivery_item_id) {
                $sdItem = \App\Models\Warehouse\IncomingOperationItem::find($item->supplier_delivery_item_id);
                if ($sdItem && $sdItem->operation && $sdItem->operation->delivery_type === 'foc') {
                    $isFocReturn = true;
                }
            }

            if ($isFocReturn) {
                // Handle FOC return - return to FOC inventory
                // Use total_qty_to_return for explicit FOC returns, or returnable_qty for supplier FOC returns
                $focReturnQty = $originalItemData['total_qty_to_return'] ?? $originalItemData['returnable_qty'] ?? 0;

                if ($focReturnQty > 0) {
                    $this->processFocReturn($item, $focReturnQty, $operation, $originalItemData);
                }
            } else {
                // Handle regular return
                $returnableQty = $originalItemData['returnable_qty'] ?? 0;

                if ($returnableQty > 0) {
                    // Determine the division_id from the original source
                    $divisionId = null;

                    // Check if this is a material request return
                    if ($item->outgoing_item_id) {
                        $outgoingItem = \App\Models\Warehouse\OutgoingItem::find($item->outgoing_item_id);
                        if ($outgoingItem && $outgoingItem->division_id) {
                            $divisionId = $outgoingItem->division_id;
                        }
                    }

                    // Check if this is a direct delivery return
                    if (!$divisionId && $operation->direct_delivery_id) {
                        $directDelivery = \App\Models\DirectDelivery::find($operation->direct_delivery_id);
                        if ($directDelivery && $directDelivery->project_division_id) {
                            $divisionId = $directDelivery->project_division_id;
                        }
                    }

                    // Set the division_id on the operation temporarily for this item processing
                    $originalDivisionId = $operation->division_id;
                    if ($divisionId) {
                        $operation->division_id = $divisionId;
                    }

                    // For all types of returns (MR, DD, MT, SD), use the standard flow
                    // Create a temporary item with only the returnable quantity
                    $tempItem = clone $item;
                    $tempItem->quantity_delivered = $returnableQty;
                    $tempItem->quantity_accepted = $returnableQty;

                    // Process only returnable quantity to inventory
                    $operation->processItemToInventory($tempItem);

                    // Restore original division_id
                    $operation->division_id = $originalDivisionId;

                    \Log::info('Processed returnable quantity to inventory', [
                        'item_id' => $item->item_id,
                        'returnable_qty' => $returnableQty,
                        'total_returned_qty' => $item->quantity_delivered,
                        'division_id' => $divisionId,
                        'return_type' => $this->determineReturnType($operation, $originalItemData)
                    ]);
                }
            }
        }
    }

    /**
     * Determine the source type from an IncomingOperationItem
     */
    private function determineSourceTypeFromItem($item)
    {
        // Check storage_location first (used for consolidated mode to store source type)
        if ($item->storage_location === 'foc_transfer') return 'foc_transfer';

        // Check for FOC fulfillment (outgoing_fulfillment_id indicates FOC transfer)
        if ($item->outgoing_fulfillment_id) return 'foc_transfer';

        if ($item->outgoing_item_id) return 'material_request';
        if ($item->direct_delivery_item_id) return 'direct_delivery';
        if ($item->supplier_delivery_item_id) return 'supplier_delivery';
        if ($item->material_transfer_item_id) return 'site_transfer';
        return 'unknown';
    }

    /**
     * Process FOC return - return items to FOC inventory
     */
    private function processFocReturn($item, $focReturnQty, $operation, $originalItemData)
    {
        try {
            // Check for supplier_delivery_item_id to get division_id for supplier FOC returns
            $divisionId = null;
            if ($item->supplier_delivery_item_id) {
                $sdItem = \App\Models\Warehouse\IncomingOperationItem::find($item->supplier_delivery_item_id);
                if ($sdItem && $sdItem->operation) {
                    $divisionId = $sdItem->operation->division_id;
                }
            }

            // Also check for outgoing_fulfillment_id to get division_id for FOC transfer returns
            if (!$divisionId && $item->outgoing_fulfillment_id) {
                $fulfillment = \App\Models\Warehouse\OutgoingFulfillment::find($item->outgoing_fulfillment_id);
                if ($fulfillment && $fulfillment->foc_inventory_id) {
                    $originalFocInventory = \App\Models\FocInventory::find($fulfillment->foc_inventory_id);
                    if ($originalFocInventory && $originalFocInventory->division_id) {
                        $divisionId = $originalFocInventory->division_id;
                    }
                }
            }

            \Log::info('Processing FOC return', [
                'item_id' => $item->item_id,
                'foc_return_qty' => $focReturnQty,
                'operation_id' => $operation->id,
                'division_id' => $divisionId
            ]);

            $remainingQty = $focReturnQty;

            // Check if this is a return from a supplier FOC delivery (has division_id)
            // If so, decrement the original supplier_foc FocInventory record
            if ($divisionId) {
                $supplierFocInventory = \App\Models\FocInventory::where('item_id', $item->item_id)
                    ->where('division_id', $divisionId)
                    ->where('source_type', 'supplier_foc')
                    ->where('quantity_available', '>', 0)
                    ->first();

                if ($supplierFocInventory) {
                    $qtyToDecrement = min($remainingQty, $supplierFocInventory->quantity_available);
                    $supplierFocInventory->quantity_available -= $qtyToDecrement;
                    $supplierFocInventory->save();

                    \Log::info('Decremented supplier FOC inventory', [
                        'foc_inventory_id' => $supplierFocInventory->id,
                        'decremented_qty' => $qtyToDecrement,
                        'remaining_qty' => $supplierFocInventory->quantity_available
                    ]);

                    $remainingQty -= $qtyToDecrement;

                    // If full quantity was decremented, just reduce project inventory and return
                    if ($remainingQty <= 0) {
                        $this->adjustProjectInventoryForFocReturn($operation->project_id, $item->item_id, $focReturnQty);

                        \Log::info('FOC return processed - decremented supplier FOC inventory', [
                            'foc_inventory_id' => $supplierFocInventory->id,
                            'quantity' => $focReturnQty,
                            'item_id' => $item->item_id,
                            'division_id' => $divisionId
                        ]);

                        return;
                    }
                }
            }

            // Create FOC inventory record for remaining quantity (or full if no supplier FOC)
            $focInventory = \App\Models\FocInventory::create([
                'item_id' => $item->item_id,
                'source_type' => 'site_return',
                'source_reference' => 'SR-' . str_pad($operation->id, 6, '0', STR_PAD_LEFT),
                'source_project_id' => $operation->project_id,
                'division_id' => $divisionId, // Track division for site_return records too
                'condition' => 'serviceable', // FOC returns default to serviceable condition
                'quantity_available' => $remainingQty,
                'status' => 'available',
                'quality_status' => 'approved',
                'quality_notes' => 'FOC Return - Returned from project site: ' . ($operation->project->project_name ?? 'project'),
                'cost_responsibility' => 'FOC Return',
                'created_by' => $operation->received_by ?? auth()->id(),
                'updated_by' => $operation->received_by ?? auth()->id(),
            ]);

            // Reduce project inventory
            $this->adjustProjectInventoryForFocReturn($operation->project_id, $item->item_id, $focReturnQty);

            \Log::info('FOC return processed successfully', [
                'foc_inventory_id' => $focInventory->id,
                'quantity' => $remainingQty,
                'item_id' => $item->item_id,
                'project_id' => $operation->project_id
            ]);

        } catch (\Exception $e) {
            \Log::error('Error processing FOC return', [
                'error' => $e->getMessage(),
                'item_id' => $item->item_id,
                'foc_return_qty' => $focReturnQty,
                'operation_id' => $operation->id
            ]);
            throw $e;
        }
    }

    /**
     * Adjust project inventory when FOC items are returned
     */
    private function adjustProjectInventoryForFocReturn($projectId, $itemId, $quantity)
    {
        // Find project inventory records for this item
        $projectInventories = \App\Models\Warehouse\ProjectInventory::where('project_id', $projectId)
            ->where('item_id', $itemId)
            ->where('quantity_available', '>', 0)
            ->orderBy('created_at', 'asc') // FIFO - oldest first
            ->get();

        $remainingToReturn = $quantity;

        foreach ($projectInventories as $inventory) {
            if ($remainingToReturn <= 0) {
                break;
            }

            $availableToReturn = min($inventory->quantity_available, $remainingToReturn);

            // Reduce project inventory
            $inventory->quantity_available -= $availableToReturn;
            $inventory->save();

            // Create stock movement record
            \App\Models\Warehouse\StockMovement::create([
                'item_id' => $itemId,
                'movement_type' => 'out',
                'reference_id' => $inventory->id,
                'reference_type' => 'ProjectInventory',
                'quantity_before' => $inventory->quantity_available + $availableToReturn, // Before reduction
                'quantity_moved' => $availableToReturn,
                'quantity_after' => $inventory->quantity_available, // After reduction
                'division_id' => null, // Can be null for FOC returns
                'user_id' => auth()->id() ?? 1,
                'notes' => 'FOC return to FOC inventory from Project: ' . ($inventory->project->project_name ?? 'Unknown'),
            ]);

            $remainingToReturn -= $availableToReturn;

            \Log::info('Project inventory adjusted for FOC return', [
                'project_inventory_id' => $inventory->id,
                'quantity_returned' => $availableToReturn,
                'remaining_in_project' => $inventory->quantity_available,
                'remaining_to_return' => $remainingToReturn
            ]);
        }

        if ($remainingToReturn > 0) {
            \Log::warning('Could not return full FOC quantity - insufficient project inventory', [
                'project_id' => $projectId,
                'item_id' => $itemId,
                'requested_return' => $quantity,
                'remaining_not_returned' => $remainingToReturn
            ]);
        }
    }

    /**
     * Process FOC return for consolidated mode
     * Returns items to FOC inventory and subtracts from project inventory
     */
    private function processFocReturnConsolidated($item, $returnQty, $operation, $divisionId = null)
    {
        try {
            \Log::info('Processing FOC return (consolidated mode)', [
                'item_id' => $item->item_id,
                'return_qty' => $returnQty,
                'operation_id' => $operation->id,
                'project_id' => $operation->project_id,
                'division_id' => $divisionId
            ]);

            // Check if this is a return from a supplier FOC delivery (has division_id)
            // If so, decrement the original supplier_foc FocInventory record
            if ($divisionId) {
                $supplierFocInventory = \App\Models\FocInventory::where('item_id', $item->item_id)
                    ->where('division_id', $divisionId)
                    ->where('source_type', 'supplier_foc')
                    ->where('quantity_available', '>', 0)
                    ->first();

                if ($supplierFocInventory) {
                    $qtyToDecrement = min($returnQty, $supplierFocInventory->quantity_available);
                    $supplierFocInventory->quantity_available -= $qtyToDecrement;
                    $supplierFocInventory->save();

                    \Log::info('Decremented supplier FOC inventory', [
                        'foc_inventory_id' => $supplierFocInventory->id,
                        'decremented_qty' => $qtyToDecrement,
                        'remaining_qty' => $supplierFocInventory->quantity_available
                    ]);

                    // If full quantity was decremented from supplier FOC, no need to create site_return record
                    if ($qtyToDecrement >= $returnQty) {
                        // Subtract from project inventory
                        $this->adjustProjectInventoryForFocReturn($operation->project_id, $item->item_id, $returnQty);

                        // Create stock movement for audit trail
                        \App\Models\Warehouse\StockMovement::create([
                            'item_id' => $item->item_id,
                            'movement_type' => 'return',
                            'reference_type' => 'foc_supplier_return',
                            'reference_id' => $operation->id,
                            'quantity_before' => $supplierFocInventory->quantity_available + $qtyToDecrement,
                            'quantity_moved' => $returnQty,
                            'quantity_after' => $supplierFocInventory->quantity_available,
                            'user_id' => $operation->received_by ?? auth()->id(),
                            'notes' => "FOC Return from supplier FOC delivery - {$operation->operation_number}"
                        ]);

                        \Log::info('FOC return processed - decremented supplier FOC inventory', [
                            'foc_inventory_id' => $supplierFocInventory->id,
                            'quantity' => $returnQty,
                            'item_id' => $item->item_id,
                            'division_id' => $divisionId
                        ]);

                        return;
                    }

                    // Partial decrement - remaining qty goes to site_return record
                    $returnQty -= $qtyToDecrement;
                }
            }

            // Create FOC inventory record for remaining quantity (or full quantity if no supplier FOC)
            $focInventory = \App\Models\FocInventory::create([
                'item_id' => $item->item_id,
                'source_type' => 'site_return',
                'source_reference' => $operation->operation_number ?? ('SR-' . str_pad($operation->id, 6, '0', STR_PAD_LEFT)),
                'source_project_id' => $operation->project_id,
                'division_id' => $divisionId, // Track division for site_return records too
                'condition' => 'serviceable',
                'quantity_available' => $returnQty,
                'status' => 'available',
                'quality_status' => 'approved',
                'quality_notes' => 'FOC Return (Consolidated) - Returned from project: ' . ($operation->project->project_name ?? 'project'),
                'cost_responsibility' => 'FOC Return',
                'created_by' => $operation->received_by ?? auth()->id(),
                'updated_by' => $operation->received_by ?? auth()->id(),
            ]);

            // Subtract from project inventory
            $this->adjustProjectInventoryForFocReturn($operation->project_id, $item->item_id, $returnQty);

            // Create stock movement for audit trail
            \App\Models\Warehouse\StockMovement::create([
                'item_id' => $item->item_id,
                'movement_type' => 'in',
                'reference_type' => 'foc_return',
                'reference_id' => $operation->id,
                'quantity_before' => 0,
                'quantity_moved' => $returnQty,
                'quantity_after' => $returnQty,
                'user_id' => $operation->received_by ?? auth()->id(),
                'notes' => "FOC Return to FOC Inventory - {$operation->operation_number}"
            ]);

            \Log::info('FOC return processed successfully (consolidated)', [
                'foc_inventory_id' => $focInventory->id,
                'quantity' => $returnQty,
                'item_id' => $item->item_id
            ]);

        } catch (\Exception $e) {
            \Log::error('Error processing FOC return (consolidated)', [
                'error' => $e->getMessage(),
                'item_id' => $item->item_id,
                'return_qty' => $returnQty,
                'operation_id' => $operation->id
            ]);
            throw $e;
        }
    }

    /**
     * Process damaged return for consolidated mode
     * Subtracts from project inventory but does NOT add to main warehouse inventory
     * Creates a record for damaged goods tracking
     * Also updates division delivery tracking for proper reporting
     */
    private function processDamagedReturnConsolidated($item, $damagedQty, $operation, $divisionId = null)
    {
        try {
            \Log::info('Processing damaged return (consolidated mode)', [
                'item_id' => $item->item_id,
                'damaged_qty' => $damagedQty,
                'operation_id' => $operation->id,
                'project_id' => $operation->project_id,
                'division_id' => $divisionId
            ]);

            // Subtract from project inventory (damaged items still leave the project)
            $projectInventory = \App\Models\Warehouse\ProjectInventory::where('project_id', $operation->project_id)
                ->where('item_id', $item->item_id)
                ->first();

            if ($projectInventory) {
                $projectInventory->updateQuantity($damagedQty, 'subtract');

                if ($projectInventory->quantity_available <= 0) {
                    $projectInventory->delete();
                }
            }

            // Update division delivery tracking if division is known
            // For damaged items: only reduce issued qty, do NOT add to warehouse stock
            if ($divisionId) {
                \App\Models\Warehouse\DivisionDelivery::recordDamagedReturn(
                    $divisionId,
                    $item->item_id,
                    $damagedQty
                );

                \Log::info('Division delivery updated for damaged return (FOC only)', [
                    'division_id' => $divisionId,
                    'item_id' => $item->item_id,
                    'damaged_qty' => $damagedQty
                ]);
            }

            // Create stock movement for damaged items (out from project, not in to warehouse)
            \App\Models\Warehouse\StockMovement::create([
                'item_id' => $item->item_id,
                'movement_type' => 'out',
                'reference_type' => 'damaged_return',
                'reference_id' => $operation->id,
                'quantity_before' => 0,
                'quantity_moved' => $damagedQty,
                'quantity_after' => 0,
                'division_id' => $divisionId,
                'user_id' => $operation->received_by ?? auth()->id(),
                'notes' => "Damaged items returned from project (not added to inventory) - {$operation->operation_number}"
            ]);

            // Create damaged item record for tracking
            $itemModel = \App\Models\Warehouse\Item::find($item->item_id);
            $unitCost = $item->unit_price ?? 0;
            \App\Models\DamagedItem::create([
                'item_id' => $item->item_id,
                'quantity' => $damagedQty,
                'unit' => $itemModel ? ($itemModel->unit ? $itemModel->unit->unit_symbol : 'PCS') : 'PCS',
                'unit_cost' => $unitCost,
                'total_cost' => $damagedQty * $unitCost,
                'source_type' => 'site_return',
                'source_reference' => $operation->operation_number ?? ('SR-' . str_pad($operation->id, 6, '0', STR_PAD_LEFT)),
                'project_id' => $operation->project_id,
                'project_details' => $operation->project ? $operation->project->project_name : null,
                'damage_description' => 'Returned damaged from project site',
                'damage_severity' => 'moderate',
                'damage_notes' => "Consolidated site return - Division ID: " . ($divisionId ?? 'N/A'),
                'cost_responsibility' => 'Project',
                'reported_by' => $operation->received_by ?? auth()->id(),
                'reported_at' => now(),
                'status' => 'reported'
            ]);

            \Log::info('Damaged return processed (consolidated)', [
                'item_id' => $item->item_id,
                'damaged_qty' => $damagedQty,
                'project_id' => $operation->project_id,
                'division_id' => $divisionId
            ]);

        } catch (\Exception $e) {
            \Log::error('Error processing damaged return (consolidated)', [
                'error' => $e->getMessage(),
                'item_id' => $item->item_id,
                'damaged_qty' => $damagedQty,
                'operation_id' => $operation->id
            ]);
            throw $e;
        }
    }

    /**
     * Determine the return type for logging purposes
     */
    private function determineReturnType($operation, $originalItemData)
    {
        if (isset($originalItemData['is_foc']) && $originalItemData['is_foc'] == '1') {
            return 'foc_transfer';
        } elseif (isset($originalItemData['supplier_delivery_item_id'])) {
            return 'supplier_delivery';
        } elseif ($operation->material_request_id) {
            return 'material_request';
        } elseif ($operation->direct_delivery_id) {
            return 'direct_delivery';
        } elseif ($operation->material_transfer_id) {
            return 'material_transfer';
        }
        return 'unknown';
    }

    /**
     * Process indirect goods submitted from the supplier delivery page.
     */
    private function processIndirectSupplierDelivery(IncomingOperation $operation, Request $request): ?IndirectGoodsIncoming
    {
        $indirectItems = collect($request->indirect_items ?? [])->filter(function ($item) {
            return !empty($item['indirect_good_id']) && floatval($item['quantity_delivered'] ?? 0) > 0;
        });

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

        $subtotal = $indirectItems->sum(function ($item) use ($operation) {
            $quantity = floatval($item['quantity_delivered'] ?? 0);
            $unitPrice = $operation->delivery_type === 'foc' ? 0 : floatval($item['unit_price'] ?? 0);

            return $quantity * $unitPrice;
        });

        $vatAmount = $subtotal * (($operation->vat_rate ?? 0) / 100);

        $incoming = IndirectGoodsIncoming::create([
            'supplier_id' => $operation->supplier_id,
            'invoice_number' => $operation->delivery_note_number ?: $operation->operation_number,
            'invoice_date' => $operation->operation_date,
            'delivery_note_number' => $operation->delivery_note_number,
            'receipt_date' => $operation->operation_date,
            'project_id' => $operation->delivery_type === 'project_site' ? $operation->project_id : null,
            'division_id' => $operation->division_id,
            'status' => 'draft',
            'received_by' => $operation->received_by,
            'approved_by' => $operation->approved_by,
            'approved_at' => now(),
            'notes' => trim(($operation->notes ? $operation->notes . "\n\n" : '') . 'Created from supplier delivery ' . $operation->operation_number . ' (' . str_replace('_', ' ', $operation->delivery_type) . ').'),
            'total_amount' => $subtotal + $vatAmount,
            'vat_amount' => $vatAmount,
            'vat_rate' => $operation->vat_rate ?? 0,
            'currency' => $operation->currency,
            'delivery_file' => $operation->delivery_file,
        ]);

        foreach ($indirectItems as $itemData) {
            $indirectGood = IndirectGood::find($itemData['indirect_good_id']);
            $quantity = floatval($itemData['quantity_delivered']);
            $unitPrice = $operation->delivery_type === 'foc' ? 0 : floatval($itemData['unit_price'] ?? 0);

            $incoming->items()->create([
                'indirect_good_id' => $itemData['indirect_good_id'],
                'quantity_ordered' => $quantity,
                'quantity_delivered' => $quantity,
                'quantity_accepted' => $quantity,
                'unit_of_measure' => $indirectGood ? $indirectGood->unit_of_measure : 'PCS',
                'unit_price' => $unitPrice,
                'batch_number' => $itemData['batch_number'] ?? null,
                'expiry_date' => $itemData['expiry_date'] ?? null,
                'notes' => 'Supplier delivery ' . $operation->operation_number,
            ]);
        }

        if ($operation->delivery_type === 'project_site') {
            $this->processIndirectSupplierDeliveryToProject($incoming, $operation);
            $incoming->status = 'completed';
            $incoming->save();
        } else {
            $incoming->complete();
        }

        return $incoming;
    }

    /**
     * Project-site supplier delivery for indirect goods should land on project stock only.
     */
    private function processIndirectSupplierDeliveryToProject(IndirectGoodsIncoming $incoming, IncomingOperation $operation): void
    {
        foreach ($incoming->items as $incomingItem) {
            $quantity = $incomingItem->quantity_accepted > 0
                ? $incomingItem->quantity_accepted
                : $incomingItem->quantity_delivered;

            if ($quantity <= 0) {
                continue;
            }

            $projectInv = IndirectGoodsProjectInventory::firstOrNew([
                'project_id' => $operation->project_id,
                'indirect_good_id' => $incomingItem->indirect_good_id,
                'division_id' => $operation->division_id,
                'invoice_number' => $incoming->invoice_number,
            ]);

            if ($projectInv->exists) {
                $projectInv->updateQuantity($quantity, $incomingItem->unit_price, 'add');
            } else {
                $projectInv->batch_number = $incomingItem->batch_number;
                $projectInv->quantity_available = $quantity;
                $projectInv->quantity_consumed = 0;
                $projectInv->unit_price = $incomingItem->unit_price;
                $projectInv->total_value = $quantity * $incomingItem->unit_price;
                $projectInv->save();
            }

            $totalBefore = IndirectGoodsProjectInventory::where('project_id', $operation->project_id)
                ->where('indirect_good_id', $incomingItem->indirect_good_id)
                ->sum('quantity_available') - $quantity;

            IndirectGoodsStockMovement::create([
                'indirect_good_id' => $incomingItem->indirect_good_id,
                'movement_type' => 'in',
                'reference_type' => 'supplier_delivery_project_site',
                'reference_id' => $operation->id,
                'quantity_before' => max(0, $totalBefore),
                'quantity_moved' => $quantity,
                'quantity_after' => $totalBefore + $quantity,
                'invoice_number' => $incoming->invoice_number,
                'division_id' => $operation->division_id,
                'user_id' => $operation->received_by,
                'notes' => 'Indirect supplier delivery to project: ' . $operation->operation_number,
            ]);
        }
    }


    /**
     * Process supplier delivery directly to project site
     * Similar to direct delivery from material issue system
     */
    private function processProjectSiteDelivery(IncomingOperation $operation, Request $request)
    {
        try {
            \Log::info("Processing project site delivery", [
                'operation_id' => $operation->id,
                'project_id' => $operation->project_id,
                'delivery_type' => $operation->delivery_type
            ]);

            // Create inventory records that track materials on project site
            foreach ($operation->items as $item) {
                $quantity = $item->quantity_delivered;
                $unitPrice = $item->unit_price;

                // Create main inventory record for tracking (material owned by warehouse but on project site)
                // Truncate location to fit within 100 characters
                $projectName = $operation->project->project_name ?? 'Unknown';
                $location = 'Project Site - ' . $projectName;
                if (strlen($location) > 100) {
                    // Truncate project name to fit, leaving room for "Project Site - " (15 chars) and "..." (3 chars)
                    $maxProjectNameLength = 82; // 100 - 15 - 3
                    $location = 'Project Site - ' . substr($projectName, 0, $maxProjectNameLength) . '...';
                }

                \App\Models\Warehouse\Inventory::create([
                    'item_id' => $item->item_id,
                    'division_id' => \App\Models\Department::first()->id ?? 1,
                    'supplier_id' => $operation->supplier_id,
                    'quantity_available' => 0, // Not available in main warehouse - it's on project site
                    'unit_price' => $unitPrice,
                    'purchase_price' => $unitPrice,
                    'vat_amount' => ($unitPrice * $quantity) * ($operation->vat_rate / 100),
                    'currency' => $operation->currency,
                    'total_value' => $quantity * $unitPrice,
                    'location' => $location,
                    'batch_number' => $item->batch_number,
                    'supplier_batch_number' => $item->batch_number,
                    'purchase_order_number' => $operation->lpo_number,
                    'production_date' => $item->production_date ?: null,
                    'expiry_date' => $item->expiry_date ?: null,
                    'status' => 'allocated', // Material exists in system but allocated to project
                    'delivery_status' => 'delivered_to_project',
                    'delivery_type' => 'direct_delivery',
                    'delivery_reference' => $operation->id,
                    'delivered_at' => $operation->operation_date,
                    'quality_status' => 'pending',
                    'last_updated' => now()
                ]);

                // Create project inventory record (material available on project site)
                $existingProjectInventory = \App\Models\Warehouse\ProjectInventory::where('project_id', $operation->project_id)
                    ->where('item_id', $item->item_id)
                    ->first();

                if ($existingProjectInventory) {
                    // Add to existing project inventory
                    $existingProjectInventory->quantity_available += $quantity;
                    $existingProjectInventory->total_value += ($quantity * $unitPrice);
                    $existingProjectInventory->save();
                } else {
                    // Create new project inventory record
                    \App\Models\Warehouse\ProjectInventory::create([
                        'project_id' => $operation->project_id,
                        'item_id' => $item->item_id,
                        'quantity_available' => $quantity,
                        'allocated_quantity' => 0,
                        'unit_price' => $unitPrice,
                        'total_value' => $quantity * $unitPrice,
                        'source_reference' => 'SUPPLIER-' . $operation->operation_number,
                        'source_type' => 'supplier_delivery',
                        'last_updated_by' => $operation->received_by,
                        'notes' => 'Direct supplier delivery to project site'
                    ]);
                }

                // Create stock movement record for supplier delivery to project site
                // Get current stock to calculate quantity_before
                $currentStock = \App\Models\Warehouse\ProjectInventory::where('project_id', $operation->project_id)
                    ->where('item_id', $item->item_id)
                    ->value('quantity_available') ?? 0;

                $projectName = $operation->project->project_name ?? 'Unknown Project';
                $now = now();

                // Stock IN - material received from supplier (audit trail)
                StockMovement::create([
                    'item_id' => $item->item_id,
                    'movement_type' => 'in',
                    'quantity_before' => 0,
                    'quantity_moved' => $quantity,
                    'quantity_after' => 0,
                    'reference_type' => 'supplier_delivery',
                    'reference_id' => $operation->id,
                    'user_id' => $operation->received_by,
                    'notes' => "Direct supplier delivery to project: {$projectName} - {$operation->operation_number}",
                    'created_at' => $now,
                    'updated_at' => $now
                ]);

                // Stock OUT - material delivered directly to project site (1 second after IN for correct ordering)
                StockMovement::create([
                    'item_id' => $item->item_id,
                    'movement_type' => 'out',
                    'quantity_before' => 0,
                    'quantity_moved' => $quantity,
                    'quantity_after' => 0,
                    'reference_type' => 'supplier_delivery',
                    'reference_id' => $operation->id,
                    'user_id' => $operation->received_by,
                    'notes' => "Direct delivery to project: {$projectName} - {$operation->operation_number}",
                    'created_at' => $now->copy()->addSecond(),
                    'updated_at' => $now->copy()->addSecond()
                ]);

                // Update division delivery tracking
                // For project site deliveries: record as delivered AND issued (since it goes directly to project)
                if ($operation->division_id) {
                    $divisionDelivery = \App\Models\Warehouse\DivisionDelivery::firstOrNew([
                        'division_id' => $operation->division_id,
                        'item_id' => $item->item_id
                    ]);

                    if ($divisionDelivery->exists) {
                        // Update existing record
                        $divisionDelivery->total_quantity_delivered += $quantity;
                        // For direct project delivery, items are issued immediately (not available in warehouse)
                        $divisionDelivery->total_quantity_issued += $quantity;
                        // current_available_quantity stays the same (items don't go to warehouse)
                        $divisionDelivery->total_value += ($quantity * $unitPrice);
                        $divisionDelivery->average_unit_price = $divisionDelivery->total_quantity_delivered > 0
                            ? $divisionDelivery->total_value / $divisionDelivery->total_quantity_delivered
                            : $unitPrice;
                        $divisionDelivery->last_delivery_date = $operation->operation_date;
                    } else {
                        // Create new record
                        $divisionDelivery->total_quantity_delivered = $quantity;
                        $divisionDelivery->current_available_quantity = 0; // Not available - went directly to project
                        $divisionDelivery->total_quantity_issued = $quantity; // Already issued to project
                        $divisionDelivery->average_unit_price = $unitPrice;
                        $divisionDelivery->total_value = $quantity * $unitPrice;
                        $divisionDelivery->first_delivery_date = $operation->operation_date;
                        $divisionDelivery->last_delivery_date = $operation->operation_date;
                    }

                    $divisionDelivery->last_updated = now();
                    $divisionDelivery->save();

                    \Log::info("Division delivery tracking updated for project site delivery", [
                        'division_id' => $operation->division_id,
                        'item_id' => $item->item_id,
                        'quantity' => $quantity,
                        'total_delivered' => $divisionDelivery->total_quantity_delivered,
                        'total_issued' => $divisionDelivery->total_quantity_issued
                    ]);
                }

                \Log::info("Project site delivery item processed", [
                    'operation_id' => $operation->id,
                    'item_id' => $item->item_id,
                    'quantity' => $quantity,
                    'project_id' => $operation->project_id
                ]);
            }

            \Log::info("Project site delivery completed successfully", [
                'operation_id' => $operation->id,
                'project_id' => $operation->project_id,
                'items_count' => $operation->items->count()
            ]);

        } catch (\Exception $e) {
            \Log::error("Project site delivery processing failed", [
                'operation_id' => $operation->id,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            throw $e;
        }
    }

    /**
     * Process supplier delivery directly to FOC inventory
     * FOC (Free of Charge) items are typically samples or promotional items
     */
    private function processFocDelivery(IncomingOperation $operation, Request $request)
    {
        try {
            \Log::info("Processing FOC delivery", [
                'operation_id' => $operation->id,
                'delivery_type' => $operation->delivery_type,
                'division_id' => $operation->division_id
            ]);

            // Create FOC inventory records for each item
            foreach ($operation->items as $item) {
                $quantity = $item->quantity_delivered;
                $unitPrice = $item->unit_price;

                // Create or update FOC inventory record
                $existingFocInventory = \App\Models\FocInventory::where('item_id', $item->item_id)
                    ->where('division_id', $operation->division_id)
                    ->where('source_type', 'supplier_delivery')
                    ->where('source_reference', $operation->operation_number)
                    ->first();

                if ($existingFocInventory) {
                    // Update existing FOC inventory
                    $existingFocInventory->quantity_available += $quantity;
                    $existingFocInventory->save();
                } else {
                    // Create new FOC inventory record
                    \App\Models\FocInventory::create([
                        'item_id' => $item->item_id,
                        'division_id' => $operation->division_id,
                        'quantity_available' => $quantity,
                        'quantity_reserved' => 0,
                        'unit' => $item->item->unit_of_measure ?? 'PCS',
                        'source_type' => 'supplier_foc',
                        'source_reference' => $operation->operation_number,
                        'condition' => 'sample',
                        'condition_notes' => 'Supplier FOC/Sample - ' . ($operation->supplier->name ?? 'Unknown Supplier'),
                        'batch_number' => $item->batch_number,
                        'supplier_batch_number' => $item->batch_number,
                        'production_date' => $item->production_date ?: null,
                        'expiry_date' => $item->expiry_date ?: null,
                        'storage_location' => 'FOC Storage',
                        'status' => 'available', // FOC items are available for use
                        'quality_status' => 'approved',
                        'estimated_value' => 0, // FOC items have no monetary value
                        'recoverable_value' => 0,
                        'received_at' => $operation->operation_date,
                        'created_by' => $operation->received_by ?? auth()->id(),
                    ]);
                }

                // Create stock movement record for FOC delivery
                $currentFocStock = \App\Models\FocInventory::where('item_id', $item->item_id)
                    ->where('division_id', $operation->division_id)
                    ->sum('quantity_available') ?? 0;

                StockMovement::create([
                    'item_id' => $item->item_id,
                    'movement_type' => 'in',
                    'quantity_before' => $currentFocStock - $quantity, // Before this delivery
                    'quantity_moved' => $quantity,
                    'quantity_after' => $currentFocStock, // After this delivery
                    'reference_type' => 'foc_supplier_delivery',
                    'reference_id' => $operation->id,
                    'user_id' => $operation->received_by,
                    'notes' => 'Supplier FOC delivery - ' . ($operation->supplier->name ?? 'Unknown Supplier'),
                ]);

                // Update division delivery tracking for FOC items
                // FOC items go to FocInventory, NOT to project sites
                // So we track them in total_quantity_delivered but NOT in total_quantity_issued
                // (total_quantity_issued is for items actually sent to projects)
                if ($operation->division_id) {
                    $divisionDelivery = \App\Models\Warehouse\DivisionDelivery::firstOrNew([
                        'division_id' => $operation->division_id,
                        'item_id' => $item->item_id
                    ]);

                    if ($divisionDelivery->exists) {
                        // Update existing record
                        $divisionDelivery->total_quantity_delivered += $quantity;
                        // FOC items do NOT get added to total_quantity_issued (they go to FocInventory)
                        // current_available_quantity stays the same (FOC items are in FocInventory, not regular inventory)
                        // FOC items have no value, so we don't update total_value or average_unit_price
                        $divisionDelivery->last_delivery_date = $operation->operation_date;
                    } else {
                        // Create new record
                        $divisionDelivery->total_quantity_delivered = $quantity;
                        $divisionDelivery->current_available_quantity = 0; // FOC items are in FocInventory
                        $divisionDelivery->total_quantity_issued = 0; // NOT issued to projects - in FocInventory
                        $divisionDelivery->average_unit_price = 0; // FOC has no price
                        $divisionDelivery->total_value = 0; // FOC has no value
                        $divisionDelivery->first_delivery_date = $operation->operation_date;
                        $divisionDelivery->last_delivery_date = $operation->operation_date;
                    }

                    $divisionDelivery->last_updated = now();
                    $divisionDelivery->save();

                    \Log::info("Division delivery tracking updated for FOC delivery", [
                        'division_id' => $operation->division_id,
                        'item_id' => $item->item_id,
                        'quantity' => $quantity,
                        'total_delivered' => $divisionDelivery->total_quantity_delivered,
                        'total_issued' => $divisionDelivery->total_quantity_issued
                    ]);
                }

                \Log::info("FOC delivery item processed", [
                    'operation_id' => $operation->id,
                    'item_id' => $item->item_id,
                    'quantity' => $quantity,
                    'division_id' => $operation->division_id
                ]);
            }

            \Log::info("FOC delivery completed successfully", [
                'operation_id' => $operation->id,
                'items_count' => $operation->items->count()
            ]);

        } catch (\Exception $e) {
            \Log::error("FOC delivery processing failed", [
                'operation_id' => $operation->id,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            throw $e;
        }
    }

    /**
     * Get all project items consolidated by item_id with their FIFO sources
     * This returns unique items with total quantities and individual source allocations for FIFO
     */
    public function getProjectItemsConsolidated($projectId)
    {
        try {
            \Log::info('getProjectItemsConsolidated called', ['project_id' => $projectId]);

            $allSources = [];
            $consolidatedItems = [];

            // Preload all project divisions for name lookup
            $divisionNames = \App\Models\Warehouse\ProjectDivision::pluck('division_name', 'id')->toArray();

            // 1. Get items from Material Requests (oldest first for FIFO)
            $materialRequests = OutgoingTransaction::with(['items.item.unit', 'project'])
                ->where('project_id', $projectId)
                ->where('status', 'successful')
                ->orderBy('transaction_date', 'asc')
                ->get();

            foreach ($materialRequests as $request) {
                $materialRequestNumber = $request->material_request_number ?: 'OUT-' . str_pad($request->id, 6, '0', STR_PAD_LEFT);

                foreach ($request->items as $item) {
                    if (!$item->item) continue;

                    $quantityReleased = $item->quantity_released ?? $item->quantity_issued ?? 0;
                    if ($quantityReleased <= 0) continue;

                    // Calculate previously returned quantity (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                        $q->where('operation_type', 'site_return')
                          ->where('project_id', $projectId)
                          ->where('status', 'completed');
                    })
                    ->where('outgoing_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate transferred to other projects
                    $transferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'outgoing_item')
                        ->where('source_id', $item->id)
                        ->sum('quantity_transferred');

                    $remainingReturnable = $quantityReleased - $previouslyReturned - $transferredToProjects;

                    if ($remainingReturnable > 0) {
                        $allSources[] = [
                            'item_id' => $item->item_id,
                            'source_type' => 'material_request',
                            'source_id' => $request->id,
                            'source_item_id' => $item->id,
                            'reference_number' => $materialRequestNumber,
                            'reference_date' => $request->transaction_date->format('Y-m-d'),
                            'reference_date_display' => $request->transaction_date->format('M d, Y'),
                            'quantity_issued' => $quantityReleased,
                            'previously_returned' => $previouslyReturned,
                            'transferred_to_projects' => $transferredToProjects,
                            'available_qty' => $remainingReturnable,
                            'unit_price' => $item->unit_price ?? 0,
                            'division_id' => $item->division_id ?? $request->division_id ?? null,
                            'division_name' => $divisionNames[$item->division_id ?? $request->division_id ?? ''] ?? null,
                            'item_description' => $item->item->item_description ?? 'Unknown Item',
                            'item_code' => $item->item->item_code ?? 'N/A',
                            'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                            'is_foc' => false,
                        ];
                    }
                }
            }

            // 2. Get items from Direct Deliveries (oldest first for FIFO)
            $directDeliveries = \App\Models\DirectDelivery::with(['items.item.unit', 'project'])
                ->where('project_id', $projectId)
                ->whereIn('status', ['delivered', 'completed'])
                ->orderBy('delivery_date', 'asc')
                ->get();

            foreach ($directDeliveries as $delivery) {
                foreach ($delivery->items as $item) {
                    if (!$item->item) continue;

                    $deliveredQuantity = $item->delivered_quantity ?? 0;
                    if ($deliveredQuantity <= 0) continue;

                    // Calculate previously returned quantity (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                        $q->where('operation_type', 'site_return')
                          ->where('project_id', $projectId)
                          ->where('status', 'completed');
                    })
                    ->where('direct_delivery_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate transferred to other projects
                    $transferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'direct_delivery_item')
                        ->where('source_id', $item->id)
                        ->sum('quantity_transferred');

                    $remainingReturnable = $deliveredQuantity - $previouslyReturned - $transferredToProjects;

                    if ($remainingReturnable > 0) {
                        $allSources[] = [
                            'item_id' => $item->item_id,
                            'source_type' => 'direct_delivery',
                            'source_id' => $delivery->id,
                            'source_item_id' => $item->id,
                            'reference_number' => $delivery->delivery_number,
                            'reference_date' => $delivery->delivery_date->format('Y-m-d'),
                            'reference_date_display' => $delivery->delivery_date->format('M d, Y'),
                            'quantity_issued' => $deliveredQuantity,
                            'previously_returned' => $previouslyReturned,
                            'transferred_to_projects' => $transferredToProjects,
                            'available_qty' => $remainingReturnable,
                            'unit_price' => $item->unit_cost ?? $item->unit_price ?? 0,
                            'division_id' => $delivery->project_division_id ?? null,
                            'division_name' => $divisionNames[$delivery->project_division_id ?? ''] ?? null,
                            'item_description' => $item->item->item_description ?? 'Unknown Item',
                            'item_code' => $item->item->item_code ?? 'N/A',
                            'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                            'is_foc' => false,
                        ];
                    }
                }
            }

            // 3. Get items from Supplier Deliveries (oldest first for FIFO)
            $supplierDeliveries = IncomingOperation::with(['items.item.unit'])
                ->where('operation_type', 'supplier_delivery')
                ->where('delivery_type', 'project_site')
                ->where('project_id', $projectId)
                ->where('status', 'completed')
                ->orderBy('operation_date', 'asc')
                ->get();

            foreach ($supplierDeliveries as $delivery) {
                foreach ($delivery->items as $item) {
                    if (!$item->item) continue;

                    $deliveredQuantity = max($item->quantity_delivered, $item->quantity_accepted) ?? 0;
                    if ($deliveredQuantity <= 0) continue;

                    // Calculate previously returned quantity (good + damaged)
                    $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                        $q->where('operation_type', 'site_return')
                          ->where('project_id', $projectId)
                          ->where('status', 'completed');
                    })
                    ->where('supplier_delivery_item_id', $item->id)
                    ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                    ->value('total') ?? 0;

                    // Calculate transferred to other projects
                    $transferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'supplier_delivery_item')
                        ->where('source_id', $item->id)
                        ->sum('quantity_transferred');

                    $remainingReturnable = $deliveredQuantity - $previouslyReturned - $transferredToProjects;

                    if ($remainingReturnable > 0) {
                        $allSources[] = [
                            'item_id' => $item->item_id,
                            'source_type' => 'supplier_delivery',
                            'source_id' => $delivery->id,
                            'source_item_id' => $item->id,
                            'reference_number' => $delivery->operation_number,
                            'reference_date' => $delivery->operation_date->format('Y-m-d'),
                            'reference_date_display' => $delivery->operation_date->format('M d, Y'),
                            'quantity_issued' => $deliveredQuantity,
                            'previously_returned' => $previouslyReturned,
                            'transferred_to_projects' => $transferredToProjects,
                            'available_qty' => $remainingReturnable,
                            'unit_price' => $item->unit_price_aed ?? $item->unit_price ?? 0,
                            'division_id' => $delivery->division_id ?? null,
                            'division_name' => $divisionNames[$delivery->division_id ?? ''] ?? null,
                            'item_description' => $item->item->item_description ?? 'Unknown Item',
                            'item_code' => $item->item->item_code ?? 'N/A',
                            'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                            'is_foc' => false,
                        ];
                    }
                }
            }

            // 4. Get items from Material Transfers (site-to-site, oldest first for FIFO)
            // Break down each transfer item into per-allocation sub-entries for proper division/FOC tracking
            $siteTransfers = \App\Models\Warehouse\MaterialSiteTransfer::with(['items.item.unit'])
                ->where('to_project_id', $projectId)
                ->where('status', 'completed')
                ->orderBy('transfer_date', 'asc')
                ->get();

            foreach ($siteTransfers as $transfer) {
                foreach ($transfer->items as $item) {
                    if (!$item->item) continue;

                    $transferredQuantity = $item->quantity_transferred ?? 0;
                    if ($transferredQuantity <= 0) continue;

                    // Load allocations for this transfer item
                    $allocations = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('site_transfer_item_id', $item->id)->get();

                    // Total transferred out from this item (shared across all allocations)
                    $totalTransferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'site_transfer_item')
                        ->where('source_id', $item->id)
                        ->sum('quantity_transferred');

                    if ($allocations->count() > 0) {
                        // Break down into per-allocation sub-entries
                        $totalAllocQty = $allocations->sum('quantity_transferred');

                        foreach ($allocations as $allocation) {
                            $allocQty = $allocation->quantity_transferred;
                            if ($allocQty <= 0) continue;

                            // Resolve division and FOC from original source
                            $resolved = \App\Helpers\AllocationDivisionResolver::resolve($allocation);

                            // Calculate per-allocation returns (using site_transfer_allocation_id)
                            $allocPreviouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                                $q->where('operation_type', 'site_return')
                                  ->where('project_id', $projectId)
                                  ->where('status', 'completed');
                            })
                            ->where('material_transfer_item_id', $item->id)
                            ->where('site_transfer_allocation_id', $allocation->id)
                            ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                            ->value('total') ?? 0;

                            // Also check returns without allocation_id (legacy) and distribute proportionally
                            $legacyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                                $q->where('operation_type', 'site_return')
                                  ->where('project_id', $projectId)
                                  ->where('status', 'completed');
                            })
                            ->where('material_transfer_item_id', $item->id)
                            ->whereNull('site_transfer_allocation_id')
                            ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                            ->value('total') ?? 0;

                            // Distribute legacy returns proportionally across allocations
                            $proportion = $totalAllocQty > 0 ? ($allocQty / $totalAllocQty) : 0;
                            $allocPreviouslyReturned += round($legacyReturned * $proportion, 3);

                            // Distribute transferred-out proportionally
                            $allocTransferredOut = round($totalTransferredToProjects * $proportion, 3);

                            $remainingReturnable = $allocQty - $allocPreviouslyReturned - $allocTransferredOut;

                            if ($remainingReturnable > 0.001) {
                                $sourceLabel = match($allocation->source_type) {
                                    'outgoing_item' => 'MR',
                                    'direct_delivery_item' => 'DD',
                                    'supplier_delivery_item' => 'SD',
                                    'foc_transfer_item' => 'FOC',
                                    'site_transfer_item', 'material_transfer_item' => 'ST',
                                    default => 'ST'
                                };
                                $refNumber = 'ST-' . str_pad($transfer->id, 5, '0', STR_PAD_LEFT) . ' [' . $sourceLabel . ']';

                                $allSources[] = [
                                    'item_id' => $item->item_id,
                                    'source_type' => 'site_transfer',
                                    'source_id' => $transfer->id,
                                    'source_item_id' => $item->id,
                                    'allocation_id' => $allocation->id,
                                    'reference_number' => $refNumber,
                                    'reference_date' => $transfer->transfer_date->format('Y-m-d'),
                                    'reference_date_display' => $transfer->transfer_date->format('M d, Y'),
                                    'quantity_issued' => $allocQty,
                                    'previously_returned' => $allocPreviouslyReturned,
                                    'transferred_to_projects' => $allocTransferredOut,
                                    'available_qty' => round($remainingReturnable, 2),
                                    'unit_price' => $resolved['is_foc'] ? 0 : ($item->unit_price ?? 0),
                                    'division_id' => $resolved['division_id'],
                                    'division_name' => $divisionNames[$resolved['division_id'] ?? ''] ?? null,
                                    'item_description' => $item->item->item_description ?? 'Unknown Item',
                                    'item_code' => $item->item->item_code ?? 'N/A',
                                    'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                                    'is_foc' => $resolved['is_foc'],
                                ];
                            }
                        }
                    } else {
                        // No allocations (legacy transfer) - show as single entry
                        $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                            $q->where('operation_type', 'site_return')
                              ->where('project_id', $projectId)
                              ->where('status', 'completed');
                        })
                        ->where('material_transfer_item_id', $item->id)
                        ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                        ->value('total') ?? 0;

                        $remainingReturnable = $transferredQuantity - $previouslyReturned - $totalTransferredToProjects;

                        if ($remainingReturnable > 0.001) {
                            $allSources[] = [
                                'item_id' => $item->item_id,
                                'source_type' => 'site_transfer',
                                'source_id' => $transfer->id,
                                'source_item_id' => $item->id,
                                'allocation_id' => null,
                                'reference_number' => 'ST-' . str_pad($transfer->id, 5, '0', STR_PAD_LEFT),
                                'reference_date' => $transfer->transfer_date->format('Y-m-d'),
                                'reference_date_display' => $transfer->transfer_date->format('M d, Y'),
                                'quantity_issued' => $transferredQuantity,
                                'previously_returned' => $previouslyReturned,
                                'transferred_to_projects' => $totalTransferredToProjects,
                                'available_qty' => round($remainingReturnable, 2),
                                'unit_price' => $item->unit_price ?? 0,
                                'division_id' => null,
                                'division_name' => null,
                                'item_description' => $item->item->item_description ?? 'Unknown Item',
                                'item_code' => $item->item->item_code ?? 'N/A',
                                'unit' => $item->item->unit ? ($item->item->unit->unit_symbol ?? $item->item->unit->unit_name ?? 'N/A') : 'N/A',
                                'is_foc' => false,
                            ];
                        }
                    }
                }
            }

            // 5. Get items from FOC Transfers (OutgoingFulfillments with fulfillment_type = 'foc_transfer')
            $focFulfillments = \App\Models\Warehouse\OutgoingFulfillment::with(['outgoingTransaction', 'item.unit'])
                ->join('outgoing_transactions', 'outgoing_fulfillments.outgoing_transaction_id', '=', 'outgoing_transactions.id')
                ->where('outgoing_transactions.project_id', $projectId)
                ->where('outgoing_fulfillments.fulfillment_type', 'foc_transfer')
                ->select('outgoing_fulfillments.*')
                ->orderBy('outgoing_fulfillments.created_at', 'asc')
                ->get();

            foreach ($focFulfillments as $fulfillment) {
                if (!$fulfillment->item) continue;

                $transferredQuantity = $fulfillment->quantity_fulfilled ?? 0;
                if ($transferredQuantity <= 0) continue;

                // Calculate previously returned quantity for FOC items (good + damaged)
                $previouslyReturned = \App\Models\Warehouse\IncomingOperationItem::whereHas('operation', function($q) use ($projectId) {
                    $q->where('operation_type', 'site_return')
                      ->where('project_id', $projectId)
                      ->where('status', 'completed');
                })
                ->where('outgoing_fulfillment_id', $fulfillment->id)
                ->selectRaw('COALESCE(SUM(quantity_delivered), 0) + COALESCE(SUM(quantity_rejected), 0) as total')
                ->value('total') ?? 0;

                $transferredToProjects = \App\Models\Warehouse\MaterialSiteTransferItemAllocation::where('source_type', 'foc_transfer_item')
                    ->where('source_id', $fulfillment->id)
                    ->sum('quantity_transferred');

                $remainingReturnable = $transferredQuantity - $previouslyReturned - $transferredToProjects;

                if ($remainingReturnable > 0) {
                    $transaction = $fulfillment->outgoingTransaction;
                    $refNumber = 'FOC-' . ($transaction->material_request_number ?: 'OUT-' . str_pad($transaction->id, 6, '0', STR_PAD_LEFT));

                    // Resolve FOC division from the outgoing transaction's items
                    $focDivisionId = null;
                    if ($transaction) {
                        $outgoingItem = \App\Models\Warehouse\OutgoingItem::where('outgoing_transaction_id', $transaction->id)
                            ->where('item_id', $fulfillment->item_id)
                            ->first();
                        if ($outgoingItem) {
                            $focDivisionId = $outgoingItem->division_id;
                        }
                    }

                    $allSources[] = [
                        'item_id' => $fulfillment->item_id,
                        'source_type' => 'foc_transfer',
                        'source_id' => $transaction->id,
                        'source_item_id' => $fulfillment->id,
                        'reference_number' => $refNumber,
                        'reference_date' => $transaction->transaction_date->format('Y-m-d'),
                        'reference_date_display' => $transaction->transaction_date->format('M d, Y'),
                        'quantity_issued' => $transferredQuantity,
                        'previously_returned' => $previouslyReturned,
                        'transferred_to_projects' => $transferredToProjects,
                        'available_qty' => $remainingReturnable,
                        'unit_price' => 0, // FOC items have no cost
                        'division_id' => $focDivisionId,
                        'division_name' => $divisionNames[$focDivisionId ?? ''] ?? null,
                        'item_description' => $fulfillment->item->item_description ?? 'Unknown Item',
                        'item_code' => $fulfillment->item->item_code ?? 'N/A',
                        'unit' => $fulfillment->item->unit ? ($fulfillment->item->unit->unit_symbol ?? $fulfillment->item->unit->unit_name ?? 'N/A') : 'N/A',
                        'is_foc' => true,
                    ];
                }
            }

            // Sort all sources by date (FIFO - oldest first)
            usort($allSources, function($a, $b) {
                return strcmp($a['reference_date'], $b['reference_date']);
            });

            $itemNotes = Item::whereIn('id', collect($allSources)->pluck('item_id')->unique()->values())
                ->pluck('notes', 'id')
                ->toArray();

            // Group by item_id and calculate totals
            foreach ($allSources as $source) {
                $itemId = $source['item_id'];

                if (!isset($consolidatedItems[$itemId])) {
                    $consolidatedItems[$itemId] = [
                        'item_id' => $itemId,
                        'item_description' => $source['item_description'],
                        'item_code' => $source['item_code'],
                        'unit' => $source['unit'],
                        'total_available_qty' => 0,
                        'total_issued' => 0,
                        'total_previously_returned' => 0,
                        'total_transferred' => 0,
                        'notes_search' => $itemNotes[$itemId] ?? '',
                        'sources' => [],
                    ];
                }

                $consolidatedItems[$itemId]['total_available_qty'] += $source['available_qty'];
                $consolidatedItems[$itemId]['total_issued'] += $source['quantity_issued'];
                $consolidatedItems[$itemId]['total_previously_returned'] += $source['previously_returned'];
                $consolidatedItems[$itemId]['total_transferred'] += $source['transferred_to_projects'];
                $consolidatedItems[$itemId]['sources'][] = $source;
            }

            // Sort by item description for easier viewing
            usort($consolidatedItems, function($a, $b) {
                return strcmp($a['item_description'], $b['item_description']);
            });

            // Re-index array
            $consolidatedItems = array_values($consolidatedItems);

            \Log::info('getProjectItemsConsolidated result', [
                'total_items' => count($consolidatedItems),
                'total_sources' => count($allSources)
            ]);

            return response()->json([
                'success' => true,
                'data' => [
                    'items' => $consolidatedItems,
                    'summary' => [
                        'total_unique_items' => count($consolidatedItems),
                        'total_sources' => count($allSources),
                    ]
                ]
            ]);

        } catch (\Exception $e) {
            \Log::error('Error in getProjectItemsConsolidated', [
                'project_id' => $projectId,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Error loading project items: ' . $e->getMessage(),
                'data' => []
            ]);
        }
    }

}
