<?php
require __DIR__ . '/vendor/autoload.php';
$app = require_once __DIR__ . '/bootstrap/app.php';
$kernel = $app->make(Illuminate\Contracts\Console\Kernel::class);
$kernel->bootstrap();

use App\Models\Warehouse\ProjectInventory;
use App\Models\Warehouse\Project;

$projectId = 681;
$project = Project::find($projectId);

echo "=== PROJECT STOCK ANALYSIS FOR PROJECT {$projectId} ===\n";
echo "Project: " . ($project ? $project->project_name : 'Unknown') . "\n\n";

$projectInventory = ProjectInventory::with('item')
    ->where('project_id', $projectId)
    ->get();

echo "=== CURRENT PROJECT INVENTORY (from project_inventories table) ===\n";
echo str_repeat('-', 100) . "\n";
printf("%-10s | %-40s | %15s | %15s\n", "Item ID", "Description", "Qty Available", "Allocated");
echo str_repeat('-', 100) . "\n";

foreach ($projectInventory as $inv) {
    $itemName = $inv->item ? substr($inv->item->item_description, 0, 40) : 'Unknown';
    printf("%-10s | %-40s | %15.2f | %15.2f\n", 
        $inv->item_id, 
        $itemName, 
        $inv->quantity_available,
        $inv->allocated_quantity
    );
}

echo "\n=== DETAILED ANALYSIS FOR ITEM 2114 ===\n";

// Get all sources of stock for this item in this project
$itemId = 2114;

echo "\n1. MATERIAL REQUESTS (Outgoing from warehouse to project):\n";
$outgoing = \App\Models\Warehouse\OutgoingTransaction::with('items')
    ->where('project_id', $projectId)
    ->whereIn('status', ['successful', 'completed', 'processed_multi_source'])
    ->get();

$totalFromMR = 0;
foreach ($outgoing as $out) {
    foreach ($out->items->where('item_id', $itemId) as $item) {
        $qty = $item->quantity_released ?: $item->quantity_issued ?: 0;
        if ($qty > 0) {
            echo "  {$out->material_request_number}: +{$qty}\n";
            $totalFromMR += $qty;
        }
    }
}
echo "  Total from MR: {$totalFromMR}\n";

echo "\n2. DIRECT DELIVERIES (from Material Issue):\n";
$directDeliveries = \App\Models\DirectDelivery::with('items')
    ->where('project_id', $projectId)
    ->whereIn('status', ['delivered', 'completed'])
    ->get();

$totalFromDD = 0;
foreach ($directDeliveries as $dd) {
    foreach ($dd->items->where('item_id', $itemId) as $item) {
        $qty = $item->delivered_quantity ?: $item->quantity_ordered ?: 0;
        if ($qty > 0) {
            echo "  DD#{$dd->id}: +{$qty}\n";
            $totalFromDD += $qty;
        }
    }
}
echo "  Total from DD: {$totalFromDD}\n";

echo "\n3. SUPPLIER DELIVERIES (Direct to project via IncomingOperation):\n";
$supplierDeliveries = \App\Models\Warehouse\IncomingOperation::with('items')
    ->where('operation_type', 'supplier_delivery')
    ->where('project_id', $projectId)
    ->where('status', 'completed')
    ->get();

$totalFromSD = 0;
foreach ($supplierDeliveries as $sd) {
    foreach ($sd->items->where('item_id', $itemId) as $item) {
        $qty = $item->quantity_delivered ?: $item->quantity_accepted ?: 0;
        if ($qty > 0) {
            echo "  {$sd->operation_number}: +{$qty}\n";
            $totalFromSD += $qty;
        }
    }
}
echo "  Total from SD: {$totalFromSD}\n";

echo "\n4. SITE RETURNS (Back to warehouse - SUBTRACT):\n";
$returns = \App\Models\Warehouse\IncomingOperation::with('items')
    ->where('operation_type', 'site_return')
    ->where('project_id', $projectId)
    ->where('status', 'completed')
    ->get();

$totalReturned = 0;
foreach ($returns as $ret) {
    foreach ($ret->items->where('item_id', $itemId) as $item) {
        $qty = $item->quantity_delivered ?: $item->quantity_accepted ?: 0;
        if ($qty > 0) {
            echo "  {$ret->operation_number}: -{$qty}\n";
            $totalReturned += $qty;
        }
    }
}
echo "  Total returned: {$totalReturned}\n";

echo "\n5. SITE TRANSFERS OUT (to other projects - SUBTRACT):\n";
$transfersOut = \App\Models\Warehouse\MaterialSiteTransfer::with('items')
    ->where('from_project_id', $projectId)
    ->where('status', 'completed')
    ->get();

$totalTransferredOut = 0;
foreach ($transfersOut as $tr) {
    foreach ($tr->items->where('item_id', $itemId) as $item) {
        $qty = $item->quantity_transferred ?: 0;
        if ($qty > 0) {
            $toProject = $tr->toProject ? $tr->toProject->project_name : 'Unknown';
            echo "  ST#{$tr->id} to {$toProject}: -{$qty}\n";
            $totalTransferredOut += $qty;
        }
    }
}
echo "  Total transferred out: {$totalTransferredOut}\n";

echo "\n6. SITE TRANSFERS IN (from other projects - ADD):\n";
$transfersIn = \App\Models\Warehouse\MaterialSiteTransfer::with('items')
    ->where('to_project_id', $projectId)
    ->where('status', 'completed')
    ->get();

$totalTransferredIn = 0;
foreach ($transfersIn as $tr) {
    foreach ($tr->items->where('item_id', $itemId) as $item) {
        $qty = $item->quantity_transferred ?: 0;
        if ($qty > 0) {
            $fromProject = $tr->fromProject ? $tr->fromProject->project_name : 'Unknown';
            echo "  ST#{$tr->id} from {$fromProject}: +{$qty}\n";
            $totalTransferredIn += $qty;
        }
    }
}
echo "  Total transferred in: {$totalTransferredIn}\n";

echo "\n=== EXPECTED vs ACTUAL ===\n";
$expected = $totalFromMR + $totalFromDD + $totalFromSD + $totalTransferredIn - $totalReturned - $totalTransferredOut;
$actual = ProjectInventory::where('project_id', $projectId)->where('item_id', $itemId)->first();
$actualQty = $actual ? $actual->quantity_available : 0;

echo "Expected: {$expected}\n";
echo "Actual (in DB): {$actualQty}\n";
echo "Difference: " . ($actualQty - $expected) . "\n";
